I have a table called Carts with a has_many relationship to another table called Subcarts. I want to create a scope that returns all instances of the Cart table where all the associated Subcarts fits a particular condition (ex: subcart.status == 1). If even one subcart that is associated to a Cart instance has a status of 1, the query should not return the cart instance.
scope :cart_bucket, -> { includes(:subcarts). where.not('subcarts.status = ?', 1). references(:subcarts)
I’ve also tried to use select within the query but had an error returned because of the return array data type. I would like to keep this within a scope and not create a method if at all possible.
Advertisement
Answer
You can select all the carts ids from those subcarts where the status is not 1 and use that as the where
clause to leave out all the carts matching those ids:
Cart.where('NOT EXISTS (SELECT s.cart_id FROM subcarts s WHERE s.cart_id = carts.id AND s.status = 1)')
The ActiveRecord version might change a bit:
Cart.where.not(id: Subcart.where(status: 1).select(:cart_id))
There the SQL generated uses a NOT IN
clause instead of NOT EXISTS
.