3

OK, I recently had great answers to a question about how to do some sub-selects in an activerecord query. I now have a more complicated situation that I can't figure out.

I want to do a search across 3 tables that have a many-to-one relationship with a :through , eg

class User << ActiveRecord::Base
  has_many :pets
  has_many :parasites, :through => :pets
end

class Pet << ActiveRecord::Base
  has_many :parasites
  belongs_to :users
end

class Parasite << ActiveRecord::Base
  belongs_to :pets
end

Now let's say I have some data like so

users

id        name
1         Bob
2         Joe
3         Brian

pets

id        user_id  animal
1         1        cat
2         1        dog
3         2        cat
4         3        dog

parasites

id        pet_id    bug
1         1         tick
2         2         flea
3         3         tick
4         3         flea
5         4         tick

What I want to do is create an active record query that will return a user that has a pet which has both ticks and fleas (i.e. User 2 - Joe)

This is so far beyond my activerecord and sql skills that I won't even bother to show you my bungled attempts so far.

Community
  • 1
  • 1
brad
  • 9,573
  • 12
  • 62
  • 89

1 Answers1

1

This is pretty much the same as the previous question, you only need to dig one level deeper on the sub-selects:

User.where('id IN (SELECT user_id FROM pets WHERE
  id IN (SELECT pet_id FROM parasites WHERE bug = ?) AND
  id IN (SELECT pet_id FROM parasites WHERE bug = ?))', 'flea', 'tick')
Jeremy Weathers
  • 2,556
  • 1
  • 16
  • 24
  • Oh great, thanks! I'm starting to figure this out. I noticed that the join disappeared this time. Going back to the previous question your answer there actually works better without the join too (the duplication disappears which I think I understand). – brad Jul 15 '11 at 06:25
  • Or in Squeel. User.where{id.in(Pet.where{id.in(Parasite.where{bug == 'flea'}.select{pet_id} & Pet.where{id.in(Parasite.where{bug == 'tick'}.select{pet_id})}.select{user_id})}. Squeel seems absolutely awesome! – brad Jul 18 '11 at 04:15