Skip to content
Advertisement

Creating a scope in Ruby on Rails, that returns an instance of an object only if all the associated instances fit a certain condition

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement