Query common Groups between two Users

Question

I'd like to query common Groups between two Users.

This are my models:

User Model

class User < ActiveRecord::Base
  has_many :group_memberships
  has_many :groups, through: :group_memberships
end

Group Model

class Group < ActiveRecord::Base
  has_many :group_memberships
  has_many :users, through: :group_memberships
end

Join Table Model between User and Group

class GroupMembership < ActiveRecord::Base
  belongs_to :user
  belongs_to :group
end

Lets say, I have two Users A and B, I'd like to get the Groups that the user A and the user B are members of in common.

For example:

User A is member of Groups [W, X, Y]

User B is member of Groups [W, X, Z]

Using this query the expected answer would be the Groups [W, X]

(Groups_From_A ∩ Groups_From_B)

I have found some answers for this, but they are not using only the active records:

user_a.groups & user_b.groups

I do not want to do this in memory, but in the database.

Answer 1

I did it!! \o/

With a little help of Mr. @zerkms answer posted here.

Well, I knew this was tricky but turned out to be easier than I thought.

The final query:

Group.joins(:users).where(users: { id: [user_a.id, user_b.id] }).group("groups.id").having("COUNT(users.id)=2")

Explanation:

I queried for the Groups from users A and B:

Group.joins(:users).where(users: { id: [user_a.id, user_b.id] }).(...)

It gave me an array with all groups from the two users, even repeated.

So I group them and get where the count where 2. Because when we have a group repeated, it means that this group were queried from User A and User B.

   (...).group("groups.id").having("COUNT(users.id)=2")

It worked fine, but if you have a better or a different solution, I'd be glad to see.

Thanks guys and thanks Mr. @zerkms, I just "translated" your answer to RoR.