@Ankhers - The error is:

** (MatchError) no match of right hand side value: %Ecto.SubQuery{cache: [:all, 0, {:offset, {:^, [], [0]}}, {:limit, {:^, [], [0]}}, {:order_by, [[desc: {{:., [], [{:&, [], [0]}, :start]}, [], []}]]}, {:where, [and: {:==, [], [{{:., [], [{:&, [], [1]}, :id]}, [], []}, {:^, [], [0]}]}]}, {:join, [{:inner, {"trips_companies", nil}, {:==, [], [{{:., [], [{:&, [], [2]}, :trip_id]}, [], []}, {{:., [], [{:&, [], [0]}, :id]}, [], []}]}}, {:inner, {"companies", Radar.Accounts.Company, 33029540}, {:==, [], [{{:., [], [{:&, [], [2]}, :company_id]}, [], []}, {{:., [], [{:&, [], [1]}, :id]}, [], []}]}}]}, {"trips", Radar.Trips.Trip, 50918967}], params: [<<145, 149, 89, 112, 52, 235, 74, 27, 151, 230, 83, 233, 8, 74, 187, 250>>, 100, 0], query: #Ecto.Query<from t0 in Radar.Trips.Trip, join: c in "trips_companies", on: c.trip_id == t0.id, join: t1 in Radar.Accounts.Company, on: c.company_id == t1.id, where: t1.id == ^"91955970-34eb-4a1b-97e6-53e9084abbfa", order_by: [desc: t0.start], limit: ^100, offset: ^0, select: %Radar.Trips.Trip{id: t0.id, mongo_id: t0.mongo_id, type: t0.type, name: t0.name, start: t0.start, finish: t0.finish, read: t0.read, muted: t0.muted, trip_type: t0.trip_type, activities: t0.activities, company_name: t0.company_name, employee_name: t0.employee_name, final: t0.final, deleted_at: t0.deleted_at, settings: t0.settings, created_at: t0.created_at, updated_at: t0.updated_at}>, select: {:struct, Radar.Trips.Trip, [id: {:value, :binary_id}, mongo_id: {:value, :string}, type: {:value, :integer}, name: {:value, :string}, start: {:value, :utc_datetime}, finish: {:value, :utc_datetime}, read: {:value, :boolean}, muted: {:value, :boolean}, trip_type: {:value, :integer}, activities: {:value, {:array, :integer}}, company_name: {:value, :string}, employee_name: {:value, :string}, final: {:value, :boolean}, deleted_at: {:value, :utc_datetime}, settings: {:value, :map}, created_at: {:value, :utc_datetime}, updated_at: {:value, :utc_datetime}]}}
    (ecto) lib/ecto/repo/queryable.ex:124: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4

@idi527 - With select instead of preloads, it seemed to work at first. However, the number of results returned was higher than the limit and so likely the joined rows were still included.

We also tried the following:

sub = Trip 
      |> join(:inner, [t], c in assoc(t, :companies)) 
      |> where([_t, c], c.id == ^company.id) 
      |> order_by([t, _c], desc: t.start) 
      |> limit(^page_size) 
      |> offset(^offset)
query = Trip 
        |> join(:inner, [t], st in subquery(sub), t.id == st.id) 
        |> join(:inner, [t, _st], d in assoc(t, :destinations)) 
        |> join(:left, [t, _st, _d], u in assoc(t, :travellers)) 
        |> order_by([t, _st, _d, _u], desc: t.start) 
        |> preload([_t, _st, d, u], destinations: d, travellers: u), 
        |> select([t, _st, _d, _u], t)

Which resulted in the number of results being slightly lower that the limit. I’ve yet to find a proper way to do this in one shot via subqueries and joins. Thoughts?