Active Record query to match every subset element

Question

In my RoR application, I've got a database lookup similar to this one:

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

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

Are there any elegant solutions for this?

Answer 1

This is not really elegant. But it should translate into the needed SQL.

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