0

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.

luickx
  • 209
  • 1
  • 2
  • 5

1 Answers1

0

since Elasticsearch is document oriented database which is why I would set the data structure somewhat differently than with conventional SQL databases. unlike SQL you usually don't join data together there.

with Subscription as Document:

// Subscription
{
    "id": "sub-1",
    "user": {"id":"user-1"}, //or   "userId": "user-1",
    "shows": [{
        "id": "show-1",
        "title": "showTitle",
        "episodes":[{"id":"episode-1"}, ...]
    }]
}

// GET /subscription-1/_search
{
    "query": {
        "nested": {
            "path": "user",
            "query": {
                "term": { "user.id": "user-1" },
                "score_mode": "avg"
            }
        }
    }
}

or other example you can also nested it into the Show document:

// Show
{
   "id": "show-1"
   "title": "showTitle",
   "episodes":[{"id":"episode-1"}, ...], //or episodeIds: ["episode-1", ...]
   "subscribers": [{"id":"user-1"}, ...] // or subscriberIds: ["user-1", ...]
}

// GET /shows-0001/_search
{
    "query": {
        "nested": {
            "path": "subscribers",
            "query": {
                "term": { "subscribers.id": "user-1" },
                "score_mode": "avg"
            }
        }
    }
}

i would also recommend this answers: https://stackoverflow.com/a/5373969/12564580

7.oz
  • 89
  • 2
  • 5