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:
- id
- media_id
- name
- language
- 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!