ecto's sql dsl basicly fixes this. here's one I just wrote in my codebase

  from(l in Blinq.Reservations.OrderCycleLinks)
    |> join(:left, [l], r in Blinq.Reservations.Reservation, on: l.reservation_id == r.id)
    |> select([l, r], %{
      order_cycle_id: l.order_cycle_id,
      customer_id: r.customer_id
    })
    |> where([l, r], l.order_cycle_id in ^order_cycle_ids and r.assignment_type == :TABLE)
    |> Repo.all()