find has_and_belongs_to_many association with multiple ids inclusive

Question

I have a Production Model with has_and_belongs_to_many relation on Tags.

I have an array of tag_ids = ['tag_one', 'tag_two'] and I want to find Productions who have those two tags or more.

Example : Production with tags ['tag_one', 'tag_two', 'tag_three'] would be return but a production with tags ['tag_one', 'tag_three'] wouldn't.

What I have tried so far :

Production.includes(:tags).where(tags: { id: ['tag_one','tag_two'] })

This request is not inclusive, it's returning production who have tag_one OR tag_two

Production.includes(:tags).where(tags: { id: ['tag_one'] }).where(tags: { id: ['tag_two'] })

This one is returning an empty array, i guess it's looking for a tag who has both ids which is impossible.

If you guys have any idea on how i can solve this it would be awesome

Thanks

Answer 1

includes method is for including related records in final resultset. Here you need to filter records using associates, which can be done with joins.

Then, records should be grouped (by id). Since tags table is joined, for every record of productions table you could have a whole list of record's tags. And depending on your situation you could apply different conditions on that tag list.

To fetch Production records which have ONLY given tags and nothing else (ie records with ['tag_one'] or ['tag_one', 'tag_two', 'tag_three'] will be ignored):

Production.joins(:tags)
          .having("array_agg(tags.id ORDER BY tags.id ASC) = ARRAY[#{tag_ids.sort.map { |t| "'#{t}'" }.join(',')}]")
          .group('productions.id')

To fetch Production records which have EVERY of given tags (ie record with ['tag_one', 'tag_two', 'tag_three'] tags will be fetched too)

Production.joins(:tags)
          .where(tags: { id: tag_ids })
          .having("count(*) = #{tag_ids.count}")
          .group('productions.id')