How do I solve this without application level overhead?

[abhisheksoni27 (Abhishek Soni)]

Problem: Find similar movies matching the given movie's name, by a particular Production House, sorted by number of total claps the movie has received.

We have three indices:

1. ProductionHouse: {"houseId", "name"} - (For the purpose of this query, we will have a predefined houseId so we will not be searching this index)
2. Movie: {"movieId", "name"}
3. stats: {"houseId", "movieId", "claps"}

I have a solution but it involves doing more work (and hence slowing down the query) at the application level.

Nevertheless, here's what I think I can do to solve this:

Solution:

Step 1. Find similar movies to the one we are given (full-text search) Let's say we get back 25 movies. Keep these IDs in a variable at the application level.

Step 2. Find all stats where houseId is what we want, and movieId is in the array of ids we got back in step 1, ordered by claps.

Step 3. Replace Ids with names.

Can you guys think of any other efficient solution?

[dadoonet (David Pilato)]

Welcome.
Could you share some sample documents?

A more efficient approach would be IMO to have everything in the same index, in the same document (aka doing joins at index time).