How to join with multiple instances of a model when using a join table with active record?

Question

This is for Rails 5 / Active Record 5 using PostgreSQL.

Let's say I have two models: Product and Widget. A Product has_many widgets and a Widget has_many products via a join table called products_widgets.

I want to write a query that finds all products that are associated with both the widget with id=37 and the widget with id=42.

I actually have a list of ids, but if I can write the above query, I can solve this problem in general.

Note that the easier version of this query is to find all widgets that are associated with either the widget with id=37 or the widget with id=42, which you could write as follows:

Product.joins(:products_widgets).where(products_widgets: {widget_id: [37, 42]})

But that isn't what I need.

Answer 1

As a starter: in pure SQL, you could phrase the query with exists conditions:

select p.*
from product p
where 
    exists (
        select 1 
        from products_widgets pw 
        where pw.product_id = p.product_id and pw.widget_id = 37
    )
    and exists (
        select 1 
        from products_widgets pw 
        where pw.product_id = p.product_id and pw.widget_id = 42
    )

In active Record, we can try and use the raw subqueries directly in where conditions:

product
    .where('exists(select 1 from products_widgets where product_id = product.product_id and widget_id = ?)', 37)
    .where('exists(select 1 from products_widgets where product_id = product.product_id and widget_id = ?)', 42)

I think that using .arel.exist might also work:

product
    .where(products_widgets.where('product_id = product.product_id and widget_id = ?', 37).arel.exists)
    .where(products_widgets.where('product_id = product.product_id and widget_id = ?', 42).arel.exists)