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.
CodePudding user response:
Does this work?
scope :awaiting_funding, ->(resubmissions: false) {
left_outer_joins(:products).where(product_type: 11, item_type: 'MyObject', products: { id: nil })
}
CodePudding user response:
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.
CodePudding user response:
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 })