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

take a look at the subquery/2 function

newer Ecto also makes it a bit more flexible Support subqueries in from and join by josevalim · Pull Request #1231 · elixir-ecto/ecto · GitHub