How to get all users that have each of these tags?

Question

User has_and_belongs_to_many tags
Tag has_and_belongs_to_many users

I'm looking for a way to get every user that has tags 2 and 5 with one query, preferably built using activerecord.

I know I could do this

users1 = Tag.find(2).users
users2 = Tag.find(5).users
usersFinal = users1 & users2

But I want to get the list of users and merge them with SQL to prevent merging arrays in ruby.

EDIT: I'm using mysql2

Answer 1

You should use the map method:

Tag.find([2,5]).map(&:users)

---

Edit2

You can also use includes:

User.includes(:tags).where(tags: { id: [2,5] })

Answer 2

Assuming you have user_tags table to support your many-to-many relationship, I would do it with the following SQL:

SELECT users.id, COUNT(user_tags.tag_id) as tags_count 
FROM users
JOIN user_tags ON users.id = user_tags.user_id
WHERE user_tags.tag_id IN (2,5)
GROUP BY users.id
HAVING COUNT(user_tags.tag_id) > 1;

Here we select users with any of given tags (eighter 2 or 5), group rows so that each row represents a single user, and check count of "nested" rows (which equals to count of references to the particular user in user_tags table). Assuming you have uniqueness validation which guarantees that there is only a single row for a pair of user and tag, user with both of them is the one who have more than 1 row "nested" (having COUNT(*) > 1).

Sorry for not being able to test it by myself, hope that gives you the insight. Refer to HAVING (SQL) function definition.