Active record request to match each element of a subset

Question

In my RoR application, I have a database search similar to this:

Client.joins(:products).where({'product.id' => [1,2,3]})

Unfortunately, this will return all customers who bought product 1, 2 or 3, but I only want to return customers who bought all of the three products. In other words, I would like to write a query that matches for n elements in a given set.

Are there any elegant solutions for this?

Answer 1

Same answer in a more dynamic way

ids = [1,2,3]

Client.joins(:products).
       where({'products.id' => ids}).
       group('users.id').
       having('COUNT(DISTINCT products.id) >= ?', ids.size)

Answer 2

It is not very elegant. But it should translate to the necessary SQL.

Client.joins(:products).
       where({'products.id' => [1,2,3]}).
       group('users.id').
       having('COUNT(DISTINCT products.id) >= 3')