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?

Maybe what you’re looking for is the `include` or `eq` methods? — Fagundes, Mar 04 '20 at 22:38
@Fagundes cant do that, `post_tags[:tag_id].eq(1).and(post_tags[:tag_id].eq(2))` wont work either. — Sandip Mane, Mar 05 '20 at 04:44

score 0 · Accepted Answer · answered Mar 05 '20 at 10:22

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!

Rails arel query for matching multiple habtm records

1 Answers1