Ecto query `in` subquery

I am having trouble trying to translate the below SQL to Ecto.Query.

select * from orders where id in (select order_id from reports)

The closest I think I come is the below but the return result is incorrect,

  from q in query,
     where: fragment("id in (select order_id from reports)")

Could this query also be:

select * from orders
inner join reports
on = reports.order_id

It looks like you want to select all of the orders that are in reports. I think an inner join can do the same without a subquery.

from o in Order,
  join: r in Report,
  on: == r.order_id
1 Like

take a look at the subquery/2 function

newer Ecto also makes it a bit more flexible