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

Question

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.

Answer 1

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.