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

user_1235929 0
Viewed 388

I’m using Rails 6.1.4.4. I have this model with a has_many

class MyObject < ApplicationRecord

    has_many :products, inverse_of: :application, as: :item

How do I write a scope that does a left outer join and also adds a condition in the LEFT-OUTER-JOIN-ON clause? I have fallen back on raw sql …

scope :awaiting_funding, ->(resubmissions: false) {

  joins("LEFT OUTER JOIN products on products.item_id = my_objects.id and products.product_type = 11 and products.item_type = ‘MyObject’”).where('products.id is null')

}

But I would like to convert this to a more Rails-like finder method.

ruby-on-rails-6 finder left-join ruby-on-rails scope
3 Answers

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.

user_48625640

Does this work?

scope :awaiting_funding, ->(resubmissions: false) {
  left_outer_joins(:products).where(product_type: 11, item_type: 'MyObject', products: { id: nil })
}
user_103352170

I will give you a much generic example of Left Outer Join

Source.
 select('a.*', 'count(b.*)').
 left_outer_joins(:b).
 joins(:c).
 where('c.body_parser = ?', true).
 group('a.id').
 having('count(b.id) = 0').
 all

Else, You can also use includes. This will generate a LEFT OUTER JOIN query

MyObject.includes(:products).where(product_type: 11, item_type: 'MyObject', products: { id: nil })
user_18532430
Related