Is there a Ruby/Rails clause for grouping by an attribute and finding groups with two different attributes?

Question

I have many Sandwich 's

I am looking to group them by a :user_id

By which that user_id group has at least one Sandwich with jelly and one with peanut butter

My best attempt is :

Sandwich.group(:user_id).having('topping = ? AND topping = ?', "jelly", "peanut butter")

But I think that syntax is geared more towards counting.

These would be matches I would want..

[
  [user_id: "X123", topping: "peanut_butter"],
  [user_id: "X123", topping: "jelly"]
],
[
  [user_id: "Y444", topping: "peanut_butter"],
  [user_id: "Y444", topping: "jelly"],
  [user_id: "Y444", topping: "jelly"]
]

Answer 1

You can achieve this in SQL using an inner join:

SELECT DISTINCT sandwiches.user_id
FROM sandwiches
INNER JOIN sandwiches sandwiches_alias
  ON sandwiches.user_id = sandwiches_alias.user_id
WHERE sandwiches.topping = "jelly"
  AND sandwiches_alias.topping = "peanut butter";

This will return just the relevant user_ids.

With this data, you can make your grouping query much simpler:

user_ids = Sandwich.connection.execute(the_above_sql).flatten
Sandwich.where(user_id: user_ids).group(:user_id)