Advice on design & query

Hi everyone I just started exploring ES it seems like a great solution to my search needs. However I am stuck at figuring out how to apply it with my current database storage design.

I have 2 tables, one called 'titles' and another called 'media' which the titles are part of.

My titles table is simple, it has 5 fields, which are:

  1. id
  2. media_id
  3. name
  4. language
  5. type

ID is the serial primary key, media_id points to the primary key in the media table which contains additional information on the media (such as type(movie/tv), year, season, status, etc.). The name is the name the title's text. Language specifies the language of the title and type specifies if the title is a synonym or an official title and such.

There are several titles that point to the same media. Currently I use Elasticsearch’s to index every row in titles. And it does provide me with great results when I search. However it returns the title's ID. I'm wondering if there is a way of grouping the results so instead of providing me with the title ID, it would provide me with the media_id.

Currently when I perform a search the top 5 results point to the same media_id which return as 5 unique title IDs. Which forces me to query my database to to get the media_id for the title ID provided. is there a better way of implementing this for my use case? I read in the docs about a 'parent' I can create for my indexes. Not sure if that is what I'd need, but it would make sense.

I hope I explained my question clearly and look forward to any advice you can throw at me.

Many thanks!

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.