3

I have a Qualifiers table storing various terms used to qualify movies. Some of them have a kind :tag or :pool and can be :active or not. They are attributed to movies through a Movietags relationship table, relatively to an edition, which changes each year.

class Qualifier < ApplicationRecord
  has_many :movies
  has_many :qualifiers, through: :movietags
  has_many :movietags, inverse_of: :qualifier, dependent: :destroy
  ...
end

class Movietag < ApplicationRecord
  belongs_to :edition
  belongs_to :movie
  belongs_to :qualifier
end

My goal is to get all the active tags (hence the two scopes) along with their movietags of any given edition (and only those). This query works and does a LEFT OUTER JOIN but excludes the qualifiers with movietags not used in the given edition:

@qualifiers = Qualifier.all_tags.active
      .includes(:movietags)
      .where('movietags.edition_id = ? OR movietags.edition_id IS NULL', @edition_id)
      .references(:movietags)


-- irrelevant parts removed for legibility
SELECT "qualifiers"."id" AS t0_r0, "qualifiers"."kind" AS t0_r1, "qualifiers"."active" AS t0_r2, 
"movietags"."id" AS t1_r0, "movietags"."edition_id" AS t1_r1, "movietags"."movie_id" AS t1_r2, 
"movietags"."qualifier_id" AS t1_r3 FROM "qualifiers" LEFT OUTER JOIN "movietags" ON 
"movietags"."qualifier_id" = "qualifiers"."id" WHERE "qualifiers"."kind" IN ('tag', 'pool') AND 
"qualifiers"."active" = $1 AND (movietags.edition_id = 5 OR movietags.edition_id IS NULL) [["active", "t"]]

How can I get all the active tags?

I'm looking for a pure Rails solution, I'm aware that direct SQL queries can achieve this (see comments, thanks @philipxy).

gemp
  • 139
  • 11
  • 1
    Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Jul 16 '20 at 17:10
  • @philipxy thanks for the feedback, but I wanted a Rails way to do it ^^ I'm trying to avoid as much as possible direct SQL queries for legibility and maintenance. I have a working Rails solution for this problem but which requires 2 queries and I was wondering if it could be done in one, but with Rails syntax. – gemp Jul 16 '20 at 21:44

0 Answers0