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

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.

As mentioned by @NikhilVengal. You should be able to use intersection of 3 scoped queries like so

scopes = [array_1,array_2,array_3].map do |arr| 
    Post.joins(:post_tags).where(PostTag.arel_table[:tag_id].in(arr)).arel
end

subquery = scopes.reduce do |memo,scope| 
    # Arel::Nodes::Intersect.new(memo,scope)
    memo.intersect(scope)
end 

Post.from(Arel::Nodes::As.new(subquery,Post.arel_table))

This should return Post objects that are the intersection of the 3 queries.

Alternatively we can create 3 joins

joins = [array_1,array_2,array_3].map.with_index do |arr,idx| 
  alias = PostTag.arel_table.alias("#{PostTag.arel_table.name}_#{idx}")
  Arel::Nodes::InnerJoin.new(
    alias,
    Arel::Nodes::On.new(
      Post.arel_table[:id].eq(alias.arel_table[:post_id])
        .and(alias.arel_table[:tag_id].in(arr))
     )
   )
end

Post.joins(joins).distinct

This will create 3 Inner joins with the Post table each being with the PostTag table filtered to the specific tag_ids ensuring that the Post will only show up if it exists in all 3 lists.

The use of AND in your where condition is checking for values that intersect (both arrays contain the same values).

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

And will return results with id: 5 since it's in both arrays.

Using an OR will get you what you need. Either one of these should work for you:

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

OR

Post.joins(:tags).where(tags: { id: array_1 + array_2 })

my idea is that you could group by posts.id and sum all its tags position in the input array position, suppose you query with 3 group_tags then you have the result like this:

post_id  group_tags_1  group_tags_2  group_tags_3 ....
1        2             0             0
2        1             1             1

so the final result is the Post with id 2 since it has at least one tag from each group.

def self.by_group_tags(group_tags)
  having_enough_tags = \
   group_tags.map do |tags|
     sanitize_sql_array(["SUM(array_position(ARRAY[?], tags.id::integer)) > 0", tags])
   end
  
   Post  
    .joins(:tags)
    .group("posts.id")
    .having(
      having_enough_tags.join(" AND ")
    )
end

# Post.by_group_tags([[1,2], [3,4]])
# Post.by_group_tags([[1,2], [3,4], [5,6,7]])

update: if you want to go to further chain and should not be effected by group, then just simple return a Relation that wrap all post ids you query from by_group_tags, such as a where as below

class Post
  def self.by_group_tags(group_tags)
    # ...
  end

  def self.scope_by_group_tags(group_tags)
    post_ids = Post.by_group_tags(group_tags).pluck(:id)
    Post.where(id: post_ids)
  end
end

# Post.scope_by_group_tags([[1,2], [3,4]]).first(10)

the drawback: call query the same Posts twice.