Rails arel query for matching multiple habtm records

Question

I have a setup as follows,

Post
- has_and_belongs_to_many :tags

Tag
- has_and_belongs_to_many :posts

THE PROBLEM:

I want to find all the posts which have social, informative tags in it, I can also use their ID's suppose 1 and 2. Using arel or ActiveRecord or plain SQL.

I can find posts having any tags using: post_tags[:tag_id].in([1, 2])

I can find posts having none tags using: post_tags[:tag_id].not_in([1, 2])

But I can't do: post_tags[:tag_id].in_all([1, 2]) that's because one record cannot match all the tag ID's ever.

How could I do this?

Answer 1

Found the answer!

posts = Arel::Table.new(:posts)
posts_tags = Arel::Table.new(:posts_tags)

tag_ids = [1, 2]

ids_predicate = 
  posts.
    project(posts[:id]).
    join(posts_tags).on(
      posts[:id].eq(posts_tags[:post_id])
    ).
    where(
      posts_tags[:tag_id].in(tag_ids)
    ).
    group(posts[:id]).
    having(
      posts_tags[:tag_id].count.eq(tag_ids.count)
    )


Post.where(posts[:id].in(ids_predicate))

Pretty cool I think!