How to Do a Left Outer Join Using Rails Activerecord

LEFT OUTER JOIN in Rails 4

You can pass a string that is the join-sql too. eg joins("LEFT JOIN StudentEnrollment se ON c.id = se.course_id")

Though I'd use rails-standard table naming for clarity:

joins("LEFT JOIN student_enrollments ON courses.id = student_enrollments.course_id")

How can I do a LEFT OUTER JOIN using Rails ActiveRecord?

I believe an includes will use a LEFT OUTER JOIN query if you do a condition on the table that the includes association uses:

Group.includes(:user_group_cmb).where(user_group_cmbs: { user_id: 1 })

How to left outer joins with conditions

Because you have a general where condition, you can use includes. This will generate a LEFT OUTER JOIN query:

Action.includes(:actions_users).where(actions_users: { user_id: true })

Or if you are using Rails 5+, Active Record provides a finder method left_outer_joins:

Action.left_outer_joins(:actions_users).where(actions_users: { user_id: true })

Rails LEFT OUTER JOIN with Subquery

I was just trying to figure this out and I don't know of any way to do it with pure Rails. But this will do what you want:

Day.joins("LEFT OUTER JOIN (
  SELECT * FROM items
    WHERE calendar_id IN (1, 4)
  ) AS items
  ON days.sdate = items.sdate
  WHERE days.sdate BETWEEN '2015-03-01' AND '2015-03-31'");

I'm not using dates in the query in my code, so I'm not sure if you need the single quotes around the dates or not. But that is the syntax. I think Left joins are very handy, I wish Rails had an actual LEFT JOIN method.

Getting ActiveRecord Query to work with a left outer join

You can achieve it using SQL NOT EXISTS

InventoryItem.where("NOT EXISTS (
   SELECT * FROM maintenance_orders 
   WHERE (maintenance_orders.inventory_item_id = inventory_items.id) 
   AND maintenance_orders.still_rentable = false)")

Rails left_outer_joins multiple conditions

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

In Rails 6, how do I add a condition to a left-outer-joins finder?

Define a new has_many

class MyObject < ApplicationRecord

    has_many :products, inverse_of: :application, as: :item
    has_many :my_object_products, -> { where(product_type: 11, item_type: 'MyObject') }, class_name: 'Product'

Now you can define your scope

scope :awaiting_funding, ->(resubmissions: false) {
   where.missing(:my_object_products)
}

This will create the query where product_type and item_type are part of the ON in the LEFT OUTER JOIN

PS: use a better name for my_object_products but you get the idea.

Rails 5 left outer join using includes() and where()

Tried a new approach using Scopes with relations, expecting to preload everything and filter it out, but was pleasantly surprised that Scopes actually give me the exact behavior I want (right down to the eager loading).

Here's the result:

This ActiveRecord Call pulls in the full list of students and eager loads the check-ins:

@students = Student.all.includes(:check_ins)

The scope of check_ins can be limited right in the has_many declaration:

Class Student < ApplicationRecord
    has_many :check_ins, -> {where('location = 'Library'}, dependent: :destroy
end

Resulting in two clean, efficient queries:

  Student Load (0.7ms)  SELECT "students".* FROM "students"
  CheckIn Load (1.2ms)  SELECT "check_ins".* FROM "check_ins" WHERE location = 'Library') AND "check_ins"."student_id" IN (6, 7, 5, 3, 1, 8, 9, 4, 2)

Bingo!

p.s. you can read more about using scopes with assocations here:

http://ducktypelabs.com/using-scope-with-associations/

---

Related Topics