Rails 5 left outer join with where() conditions excludes rows from left table

Viewed 66

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).

0 Answers
Related