How to select orders which all of its order details state is complete

Questions / Help
,
1 
orders:
id state

order_details:
id order_id state

order has_many order_details

How to select orders where with its order details state is complete. if an order has 5 order details and 4 is complete, it’s not i want.

2

Have a look at the intersect/2 function.

You should get what you need if you intersect the result of 2 OrderDetail queries, each grouped by order_id. One selecting a count of all orders where state is complete, and the other selecting a count of all orders. This will give you only the results where the counts are the same.

3

A couple of other options would be:

Add an association for complete orders, i.e. filtering associations

Use a preload query, i.e. preload queries

4

@03juan @riebeekn Thanks for your help

I can get order ids by

q1 = from s in OrderDetail, where: s.state == "complete", group_by: s.order_id, select: {s.order_id, count(s.order_id)}
q2 = from s in OrderDetail, group_by: s.order_id, select: {s.order_id, count(s.order_id)}, intersect: ^q1
order_id_and_counts = Repo.all(q2)
order_ids = order_id_and_counts |> Enum.map(fn x -> elem(x,0) end) |> Enum.sort(:asc)

next i need to query orders in these ids.

but in raw sql, i can get all orders with all order details complete state.

select * from orders o
where id in
 ( 
   select order_id from order_details od
   where state='complete'  
   group by order_id
   having count(1) = (select count(1) from order_details od2 where 
          od.order_id=od2.order_id) 
  )
order by id asc

is there a better way to do this?

5

Yes, you can do that with subquery/2 if you select into a map:

d_complete =
  from od in OrderDetails,
    where: od.state == "complete",
    select: %{oid: od.order_id, count: count()},
    group_by: od.order_id

d_intersect =
  from od in OrderDetails,
    select: %{order_id: od.order_id, count: count()},
    group_by: od.order_id,
    intersect: ^d_complete

orders = from o in Order, join: od in subquery(d_intersect), where: o.id == od.order_id

The sql ends up as:

SELECT o0."id",
  o0."inserted_at",
  o0."updated_at"
FROM "orders" AS o0
  INNER JOIN (
    SELECT so0."order_id" AS "order_id",
      count(*) AS "count"
    FROM "order_details" AS so0
    GROUP BY so0."order_id"
    INTERSECT
    (
      SELECT o0."order_id",
        count(*)
      FROM "order_details" AS o0
      WHERE (o0."state" = 'complete')
      GROUP BY o0."order_id"
    )
  ) AS s1 ON TRUE
WHERE (o0."id" = s1."order_id")
1 Like
6

If you ever manage to rewrite the query with having/3 instead of intersect, please let us know. I’m interested in the EXPLAIN performance difference on a large enough table.

7

If your state column is non nullable

SELECT * FROM orders As O Where O.id Not in 
  (Select  OL.order_id from order_details as OL 
    where OL.state != 'complete')

if state can be null - use this

SELECT * FROM orders As O Where O.id Not in 
  (Select  OL.order_id from order_details as OL 
    where OL.state =  NULL or OL.state != 'complete')
1 Like
8

So clean. Thanks!

1 Like