select all orders where order details state is complete

Question

orders:
id state

order_details:
id order_id state

order has_many order_details

i need to select all orders with its order details state is complete? i mean if an order have 5 order details, its order details must be complete.

and How to implement this in elixir ecto query?

Answer 1

If it is standard SQL, you could get what you are looking for with below Query :

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) 
  )