Порядок получения записей по количеству связанных ассоциаций "многие ко многим"

Question

Scnerio:

https://www.funtraker.com/ перечисляет фильмы, телешоу и игры. На странице показа каждого ресурса (Movie, Tv Show и т. Д.) Мы хотим перечислить связанные ресурсы.

Схема:

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

Теперь я хочу получить список связанных фильмов, основанных на подобранном жанре (два фильма связаны, если у обоих есть жанр комедии). И эти похожие фильмы нужно заказывать по максимальному количеству совпадающих жанров.

Ну вот примеры фильмов и ожидаемый результат.

#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] 

Надеюсь, вопрос имеет смысл.

ОБНОВЛЕНИЕ: ищет решение только для SQL. Мне не нужно кэшировать результаты в любой другой таблице.

Answer 1

Вам нужно упорядочить по количеству групп идентификаторов фильмов после объединения сresource_genresвзгляните на следующие методы чистого SQL:

Метод № 1 (одиночный запрос)

Двойное присоединение к resource_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

---

Метод № 2 (2 запроса)

Выщипывание genre_ids от себя resource_genres по отдельному запросу.

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

Если вы нашли решение в коде rails, то это может решить вашу проблему.

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