We are saving one item for each user, show, and episode. We save everything on the same index.
The ERD would look something like this:
┌────┐ ┌────────────┐ ┌────┐
│User├─────<┤Subscription├>────┤Show│
└────┘ └────────────┘ └─┬──┘
│
│
/|\
┌───────┐
│Episode│
└───────┘
And the items would look something like this:
User:
{ id: user-1, name: one }
Show:
{ id: show-1, title: showTitle }
Episode:
{ id: show-1, title: episodeTitle, showId: show-1, createdAt: today }
We want to do a subscription page like youtube, we want to list all the episodes from all the shows the user has subscribed to. If possible we would like to achieve this on a single query.
On PostgreSQL i would have used IN, with a query like this:
SELECT * FROM Episode WHERE show_id IN (SELECT show_id FROM Subscription WHERE user_id = 'userId')
Does ES provide a way to achieve this?
We are thinking on using multiple queries, one for each subscription, but if possible we would like to delegate all this to a single Elasticsearch query.