Query in ActiveRecord for objects that contain one or more ids from multiple arrays

Question

I have Rails 5.2 project with three models:

class Post
  has_many :post_tags
  has_many :tags, through: :post_tags
end

class PostTags
  belongs_to :post
  belongs_to :tag
end

class Tags
  has_many :post_tags
  has_many :posts, through: :post_tags
end

I have the a number of arrays of tag ids, e.g:

array_1 = [3, 4, 5]
array_2 = [5, 6, 8]
array_3 = [9, 11, 13]

I want a query that will return posts that are tagged with at least one tag with an id from each of the arrays.

For instance, imagine I have a post with the following tag ids:

> post = Post.find(1)
> post.tag_ids
> [4, 8]

If I ran the query with array_1 and array_2 it would return this post. However if I ran it with array_1, array_2 and array_3 it would not return this post.

I attempted this with the following query:

Post.joins(:tags).where('tags.id IN (?) AND tags.id IN (?)', array_1, array_2)

But this does not return the post.

What should the query be to return the post?

Any help would be greatly appreciated!

Answer 1

Since you've tagged this question with postgresql you can perform the query you want using the intersect keyword. Unfortunately, activerecord doesn't natively support intersect so you'll have to build sql to use this method.

array_1 = [3, 4, 5]
array_2 = [5, 6, 8]

query = [array_1, array_2].map do |tag_ids|
  Post.joins(:tags).where(tags: { id: tag_ids }).to_sql
end.join(' intersect ')

Post.find_by_sql(query)

Edit:

We can use subqueries to return the posts and maintain the activerecord relation.

array_1 = [3, 4, 5]
array_2 = [5, 6, 8]

Post
  .where(post_tags: PostTag.where(tag_id: array_1))
  .where(post_tags: PostTag.where(tag_id: array_2))

For bonus points, you can turn where(post_tag: PostTag.where(tag_id: array_1)) into a scope on Posts and chain as many of them as you'd like.