ruby rails activerecord nested select

Question

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'
)

Zach Kemp · Accepted Answer · 2012-10-06 23:09:12Z

1

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.

answered Oct 6, 2012 at 23:09

Zach Kemp

11.9k1 gold badge35 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Brown Over a year ago

I think you'd need to join hits as well: .joins(:hits => :tags)

hog Over a year ago

Going to try, but i think that will do an or instead of an and. (all recordings wher tag_id is 5 or 6 or 7)

Collectives™ on Stack Overflow

ruby rails activerecord nested select

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related