Rails where not in array

Question

I have some posts that each has many tags.

I need to create a query that matches against the posts that are not having the exact tags as in an array.

Also it should return all the posts that are not having tags at all with that query.

Here is what I tried:

  query = posts.joins('LEFT JOIN post_tags ON post.id = post_tags.post_id')
  no_tags = ['test', 'test1']
  query = query.where('post_tags.name NOT IN (?) OR post_tags.name IS ?', no_tags, nil) if no_tags

This gives me all the posts that are having no tags at all which is correct, also it gives me the posts that haven't the tags specified, and no other tags, which is also correct.

The problem is that if a post has multiple tags, say test, another, it returns it in the query, which is not the behaviour I want.

I need to filter these posts out, if a post has a tag that is in the array, whether another tags are included or not.

Example:

post1 has tag `test`
post2 has tag `another`
post3 has tags `test, another`
post4 has no tags

It should return:

[post2, post4]

But now it returns:

[post2, post3, post4]

Answer 1

Joining a post that has two tags with post_tags returns two rows.

post_name   post_tag
test3       test
test3       another

You should now understand why 'test3' is included in your result. The check is done for each row result and one of them passes as valid.

Consider doing something like

no_tags = ['test', 'test1']
query = query.where('post.id NOT IN (SELECT post_id FROM post_tags WHERE name IN (?))', 
  no_tags) if no_tags

Answer 2

Firstly, you should select all posts which have no_tags, call it excluded_posts. Then you make a query to get all posts that different from the excluded_posts. Here is Rail code example:

excluded_post_ids = PostTag.where.not(name: no_tags).select(:post_id)
expected_post = Post.where.not(id: excluded_post_ids)