astjohn

Help with subquery including joins and preloads for pagination

Hi All,

I’m trying to perform the following SQL query:

SELECT t.*, d.*, u.* FROM 
(
    SELECT x.* FROM trips AS x
    INNER JOIN trips_companies AS tc ON tc.trip_id = x.id
    INNER JOIN companies AS c ON tc.company_id = c.id    
    WHERE c.id = '91955970-34eb-4a1b-97e6-53e9084abbfa'
    ORDER BY x.start DESC
    LIMIT 100 
    OFFSET 0
) AS t
INNER JOIN destinations AS d ON d.trip_id = t.id
LEFT JOIN trips_travellers AS tt ON tt.trip_id = t.id
LEFT JOIN users AS u ON tt.user_id = u.id
ORDER BY t.start DESC

My first attempt was the following:

page_number = 1
page_size = 100
offset = page_size * (page_number - 1)

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 = subquery(sub)
        |> join(:inner, [t], d in assoc(t, :destinations))
        |> join(:left, [t, _d], u in assoc(t, :travellers))
        |> order_by([t, _d, _u], desc: t.start)
        |> preload([_t, d, u], destinations: d, travellers: u)

I’ve tried a bunch of combinations without any luck. As soon as the preload is introduced, an error is thrown. The only thing I haven’t tried is the use of fragments. Is there a way to do it without fragments?

Thanks!

4 comments

/phoenix #ecto #sql

1 3500 4

2019-01-26 16:48:56 UTC

Most Liked

astjohn

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

Post #4