PostgreSQL: query records where ALL joined record attributes match where clause

Question

I found a similar question but it didn't satisfy my answer: SQL: Select records where ALL joined records satisfy some condition

I have two tables, orders and shipments

orders have_many shipments

shipments have attribute status open/closed

I would like to query orders, where all of its shipments are closed:

Assuming table of:

• order1, 2 shipments: 1open, 1closed
• order2, 3 shipments: 1open, 2closed
• order3, 1 shipments: 0open, 1closed
• order4, 2 shipments: 0open, 2closed

Running the query returns records for order3 and order4

I am currently doing this with N+1 using application code, I'd like to just implement in SQL.

Answer 1

Let's look for all orders for which there does not exist any shipment (matching that order) with a status other than closed.

Order.where("NOT EXISTS(SELECT 1 FROM shipments WHERE shipments.order_id = orders.id AND status != 'closed')"

Answer 2

demo:db<>fiddle

Aggregate the status and then you can filter with the ALL operator which checks if all array elements fit the condition.

SELECT order_id
FROM (
    SELECT order_id, array_agg(status) status
    FROM shipments
    GROUP BY order_id
) s
WHERE 'closed' = ALL(status)