Get record order by number of related associations. Many to Many

Question

Scnerio:

https://www.funtraker.com lists movies, TV shows and games. On the display page of each resource (Movie, Tv Show, etc.), we want to list related resources.

Scheme:

class Movie < AR::Base has_many :resource_genres, as: :resource has_many :genres, through: :resource_genres end class ResourceGenre belongs_to :resource, polymorphic: true end 

Now I want to get a list of related films based on the matching genre (two films are related if both have a comedy genre). And these related films need to be ordered by the maximum number of agreed genres.

Well here are examples of films and the expected result.

 #Input Movie Genres Movie 1: horror, comedy, action, war Movie 2: action, thriller, crime, animation Movie 3: comedy, war, action, thriller Movie 4: crime, animation, action, war #Expected output movie1.related_movies => [ movie3, movie2 ] movie4.related_movies => [ movie2, remaining-three-movies-in-any-order ] movie3.related_movies => [ movie1, movie2, movie4] 

I hope the question will make sense.

UPDATE: SQL-only solution search. I do not need to cache the results in any other table.

Answer 1

You need to order by the number of movie ID groups after resource_genres attached to them, take a look at the following pure SQL methods:

Method # 1 (single request)

Double joining the resource_genres table to save identifiers of personal genres:

 def related_movies Movie.select("movies.*, COUNT(*) AS group_count"). joins(:resource_genres). joins("JOIN resource_genres rg ON rg.genre_id = resource_genres.genre_id"). where("rg.resource_type = 'Movie' AND rg.resource_id = ? AND movies.id != ?", self.id, self.id). group('movies.id'). order('group_count DESC') end 

---

Method No. 2 (2 requests)

Extract genre_ids from self resource_genres in a separate request.

 def related_movies Movie.select("movies.*, COUNT(*) AS group_count").joins(:resource_genres). where("resource_genres.genre_id IN (?) AND movies.id != ?", self.resource_genres.pluck(:genre_id), self.id). group('movies.id'). order('group_count DESC') end 

Answer 2

If you find a solution in rails code, it may solve your problem.

 def related_movies scores_hash = {} Movie.joins(:resource_genres).where('resource_genres.genre_id' => resource_genres.pluck(&:genre_id)).where.not(id: self.id).distinct.find_each do |movie| scores_hash[movie] = (movie.resource_genres.pluck(:genre_id) & self.resource_genres.pluck(:genre_id)).count end Hash[scores_hash.sort_by { |movie, score| -score }].keys end