Rails left_outer_joins multiple conditions

Question

I have this sql query which I'm trying to convert to an ActiveRecord query:

SELECT DISTINCT name FROM `languages` 
  LEFT OUTER JOIN `items` ON `items`.`language` = `languages.id` 
  AND (`items`.`version_id` = '1.0') 
  WHERE `languages`.`id` != 'en';

What I am trying to do is get all languages which are not present in the items table where items.version_id is for example, '1.0'. So lets say for simplicity, the languages table contains English, French and German and the items has a single row with version '1.0' and language 'en', I should then get French and German back with the above query.

(I'm no database expert so if some has a better query than the one above, I'm all ears).

I have the following models:

# item.rb
class Item < ApplicationRecord
  belongs_to :language
  belongs_to :version
end

# language.rb
class Language < ApplicationRecord
  has_many :items
end

# version.rb
class Version < ApplicationRecord
  has_many :items
end

I've tried a few different approaches which haven't worked out, for example on the console:

Language.left_joins(:items).where(items: {version_id: '1.0'})
  .where.not(languages: {id: Item.distinct.pluck(:language_id)})

This generates the following two queries which results in an empty set:

SELECT DISTINCT `items`.`language_id` FROM `items`
=>   Language Exists (0.5ms)  SELECT  1 AS one FROM `languages` 
  LEFT OUTER JOIN `items` ON `items`.`language_id` = `languages`.`id` 
  WHERE `items`.`version_id` = '1.0' 
  AND (`languages`.`id` != 'en') LIMIT 1

Note how the order of WHERE and AND are swapped.

So I'm clearly not structuring the query right and would love to know how to approach it the rails way. Is it also possible to reduce the querys down to one database query?

I'm new to Rails and still learning about scopes etc, so I bundled everything into a single ActiveRecord query.

score 2 · Answer 1 · answered Apr 23 '21 at 16:50

You will need to make use of some custom SQL in your query, at least I don't see another way. Also using select instead of pluck will turn that into a subquery.

Language
  .joins("LEFT JOINS items on items.language_id = languages.id 
          AND items.version_id = '1.0'")
  .where.not(languages: {id: Item.distinct.select(:language_id)})

max · Accepted Answer · 2021-05-06T00:40:48.753

You need to add a condition on items.id to filter the resulting rows:

languages, items = Language.arel_table, Item.arel_table
conditions = items[:language_id].eq(languages[:id])
                                .and(items[:version_id].eq('1.0'))
j = languages.outer_join(items)
             .on(conditions).join_sources

Language.joins(j)
        .where(
          items: { id: nil }
        )
        .where.not(language: { id: 'en'})

SELECT "languages".*
FROM   "languages"
       LEFT OUTER JOIN "items"
                    ON "items"."language_id" = "languages"."id"
                       AND "items"."version_id" = '1.0'
WHERE  "items"."id" IS NULL
       AND "languages"."id" != ?

You could also use WHERE languages.id NOT IN(subquery):

Language.where.not(
  id: Language.joins(:items)
              .where(items: { version_id: '1.0' })
).where.not(id: 'en')

SELECT "languages".*
FROM   "languages"
WHERE  "languages"."id" NOT IN (SELECT "languages"."id"
                                FROM   "languages"
                                       INNER JOIN "items"
                                               ON "items"."language_id" =
                                                  "languages"."id"
                                WHERE  "items"."version_id" = ?)
       AND "languages"."id" != ?

Or NOT EXISTS which may or may not have performance benefits depending on your RDBMS:

Language.where(
  Item.select(:id)
      .where(
          # items.version_id = languages.id
          Item.arel_table[:language_id].eq(Language.arel_table[:id])
       )
       .where(items: { version_id: '1.0'})
       .arel
       .exists.not

).where.not(id: 'en')

SELECT "languages".*
FROM   "languages"
WHERE  NOT ( EXISTS (SELECT "items"."id"
                     FROM   "items"
                     WHERE  "items"."language_id" = "languages"."id"
                            AND "items"."version_id" = ?) )
       AND "languages"."id" != ?
LIMIT  ?

See Difference between EXISTS and IN in SQL?.

Rails left_outer_joins multiple conditions

2 Answers2