CODEWITHSUNDEEP
ruby-on-railsjoinactiverecord
Josh Kestenberg
Josh Kestenberg

Reputation: 119

Trying to select all objects where at least one associated object doesn't match the query

I have two associated models. A Product has many DescriptorFiles, and a DescriptorFile has a boolean attribute approved.

I want to find all products where none of the associated descriptor_files are approved.

That is to say: I want to find all products where ALL descriptor_files are approved: false.

I've just come up with a solution but I'd prefer to do it all with active record queries:

Product.all - Product.joins(:descriptor_files).where(descriptor_files: {approved: true})

This works, but as stated, it'd be great if I could return an active record object.

Thanks!

Upvotes: 0

Views: 128

Answers (3)

Anand
Anand

Reputation: 6531

Alternatively, you can use left_outer_joins for rails 5 +

results = Product.left_outer_joins(:descriptor_files)
                      .where(descriptor_files: {approved: false})

Upvotes: 0

Phenixer
Phenixer

Reputation: 46

You can LEFT JOIN descriptor files that are approved and make sure you only retrieve records that has joined nothing. (This would also query Products with no descriptor files at all).

Product.joins('LEFT OUTER JOIN descriptor_files ON descriptor_files.product_id = product.id AND descriptor_files.approved = true')
       .where(descriptor_files: { id: nil })

Unfortunately the join has to be written manually, though you could use Arel to generate a Join clause.

Upvotes: 1

engineersmnky
engineersmnky

Reputation: 29318

In rails 5+ you can use where.not So in this case your query would be 

Product.where.not(id: 
  Product.select(:id).joins(:descriptor_files)
      .where(descriptor_files: {approved: true})
)

This will use a sub query of your original version as a NOT IN clause. Resulting SQL 

SELECT 
  products.* 
FROM 
  products
WHERE 
  products.id NOT IN ( 
    SELECT 
      products.id
    FROM 
      products
      INNER JOIN descriptors ON descriptors.product_id = products.id
    WHERE 
      descriptors.approved = true
  )

Upvotes: 1

Related Questions