CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecordnested
hog
hog

Reputation: 29

ruby rails activerecord nested select

I have three tables/models.

Recordings (id)
has_many :hits
has_many :tags, :through => :hits

Hits (id, recording_id, tag_id)
belongs_to :recordings
belongs_to :tags

Tags (id)
has_many :hits
has_many :recordings, :through => :hits

I need to find all recordings that have all the tags that are passed in as a parameter. For example, find all recordings that have tag.id == 5, tag.id == 6, and tag.id ==7. (although, that could be 2 tag ids, or 2000 tag ids)

I'm trying to do this with a query to the db so that it's fast instead of bringing back a large set and reducing it with ruby.

The SQL query would like something like this, I think:

SELECT * FROM recordings 
WHERE id IN (
  SELECT recording_id FROM hits 
  WHERE recording_id IN (
    SELECT recording_id FROM hits 
    WHERE recording_id IN (
      SELECT recording_id from hits WHERE recording_id = 5
    )
    AND tag_id = '6'
  ) 
  AND tag_id == '7'
)

Upvotes: 1

Views: 1007

Answers (1)

Zach Kemp
Zach Kemp

Reputation: 11904

Recording.joins(:tags).where(:tags => { :id => [5,6,7] })

I think this should work (I'm not sure it will on the :through association, though). Give it a try.

Upvotes: 1

Related Questions