Ecto query `in` subquery

Questions / Help
1

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

Could this query also be:

select * from orders
inner join reports
on orders.id = 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: o.id == r.order_id
1 Like
3

take a look at the subquery/2 function

https://hexdocs.pm/ecto/Ecto.Query.html#subquery/2

newer Ecto also makes it a bit more flexible https://github.com/elixir-ecto/ecto/pull/1231

2 Likes