CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordfiltering
kernification
kernification

Reputation: 523

Filter activerecord by nested ID and value combination in Ruby on Rails

Lets say I have two models with a many to many relationship: Item and Property

Now I have an array of properties and I want to filter all items which properties match a given value (lets say a boolean value: property.value = true)

When I try 

@items = Item.includes(:properties).where(:properties => {:id => [1,2,3].to_a, :value => true})

I would like to get all items where property(1) is true AND property(2) is true and so on. But with the code above I get all items related to the property id's and where any property is true. How should I change my code?

I would appreciate not to use a gem for this.

Upvotes: 0

Views: 976

Answers (1)

spickermann
spickermann

Reputation: 106802

Looks like you are almost there:

property_ids = [1,2,3]
Item.joins(:properties).
     where(:properties => { :id => property_ids, :value => true }).
     group('items.id').
     having('COUNT(properties.id) >= ?', property_ids.size)

joins does an INNER JOIN and is preferred over includes when you really need to join tables.

where is basically the conditions you already had, the only change is that there is not need to call to_a on the array.

Than you have to group to make that COUNT in SQL work.

having extracts the lines that have at least the expected number of property lines matching the condition.

Upvotes: 1

Related Questions