How to select a record by has-many association?

Question

I have User has_many Badges. When I want to retrieve all users who has gold badge, I just write:

User.joins(:badges).where(:id => gold.id)

But how to retrieve all users who has gold badge AND silver badge? I tried thi:

User.joins(:badges).where(:id => gold.id).where(:id => silver.id)

Obviously I got nil. What's the right way?

To clarify: I want

the users who have at least two badges: gold badge and silver badge

Or you can say:

the intersection of 
  'users who have gold badge' and 'users who have silver badge'

not

the users who have a badge which is gold and silver

Ok. so which is more efficient? JOIN twice, or INTERSECT? – Lai Yu-Hsuan Dec 19 '11 at 11:04 — Lai Yu-Hsuan, Dec 19 '11 at 11:04

score 0 · Answer 1 · answered Dec 17 '11 at 21:05

0

The or operator is not supported yet in Arel. Maybe you should take a look at the Arel Readme

If you want to use a scope as I wanted, I ended up to sum up two scopes:

where(:name => 'Red Fox') + where(:age > 28)

answered Dec 17 '11 at 21:05

dsci

score -2 · Answer 2 · answered Dec 17 '11 at 04:01

-2

How about:

User.joins(:badges).where(:id => [gold.id, silver.id])

Hope this helps!

answered Dec 17 '11 at 04:01

andrewpthorp

That's gold *or* silver. – mu is too short Dec 17 '11 at 04:20
To do an AND, you would do where("id = ? AND id = ?", silver.id, gold.id). However, that is basically the same as what you had before. I think you need to explain your question better, because id cant be two things... – andrewpthorp Dec 17 '11 at 16:09
I want 'the users who have at least two badges: gold badge and silver badge', not 'the users who have a badge which is gold and silver'. Or in SQL terms, I want the intersection of 'users who have gold badge' and 'users who have silver badge'. – Lai Yu-Hsuan Dec 17 '11 at 17:23

2 Answers2