CODEWITHSUNDEEP
ruby-on-railsactiverecord
james
james

Reputation: 4047

ActiveRecord: Querying for parent record where child records have certain values in an AND query

Normally I understand that if you pass an array of values to an ActiveRecord query it executes it like an OR statement. That is to say:

Parent.joins(:kiddos).where(kiddos: {rando_attr:[1,2]})

This query looks for Parent objects where the parent object has a kiddo object that has rando_attr of EITHER 1 OR 2.

In my case however, I want to look for Parent object where the parent object has a kiddo object with rando_attr of 1, AND ALSO has a kiddo object with rando_attr of 2.

How would I write this query?

Upvotes: 0

Views: 1267

Answers (2)

Joel Blum
Joel Blum

Reputation: 7888

Indeed the array syntax makes an IN query (which is like a logical or), an alternative to grouping (@Lam's answer) can be using 2 wheres

Parent.joins(:kiddos).where(kiddos: {rando_attr: 1}).where(kiddos: {rando_attr: 2})

Upvotes: 0

Lam Phan
Lam Phan

Reputation: 3811

Parent.joins(:kiddos)
.where(kiddos: {rando_attr:[1,2]})
.group(:id).having('COUNT(DISTINCT(kiddos.rando_attr)) = 2')

Upvotes: 1

Related Questions