Help with subquery including joins and preloads for pagination

Questions / Help
, ,
1

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!

2

What if you try select instead of preload?

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)
  |> select([t, d, u], %{t | destinations: d, travellers: u})

Something like this worked for me once. But a better solution is probably possible.

3

What is the error that you are getting with the preload?

4

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

1 Like
5

I’ve been working on something myself. I’ve run into a similar issue where if you have multiple preloaded entries I believe it starts limiting by the preloads as well. Definitely returns an odd form of the data, it’s hard to recognize a pattern. Did you ever come up with a solution?

Here is my module:

defmodule App.Pagination do
  import Ecto.Query
  alias App.Repo

  def paginate(query, page, per_page) when is_binary(page) do
    paginate(query, String.to_integer(page), per_page)
  end

  def paginate(query, page, per_page) do
    count = total_entries(query) |> Repo.one()

    paged_results = paged_query(query, page, per_page) |> Repo.all()
    {results, has_next} = split_results(per_page, paged_results)
    has_prev = page > 1

    %{
      has_next: has_next,
      has_prev: has_prev,
      prev_page: if(has_prev, do: page - 1, else: nil),
      page: page,
      next_page: if(has_next, do: page + 1, else: nil),
      last: if(rem(count, per_page) == 0, do: div(count, per_page), else: div(count, per_page) + 1),
      count: count,
      results: results
    }
  end

  defp paged_query(query, page, per_page) do
    offset = per_page * (page - 1)

    query
  |> limit(^(per_page + 1))
  |> offset(^offset)
  end

  defp split_results(per_page, paged_results) when length(paged_results) <= per_page do
    {paged_results, false}
  end

  defp split_results(per_page, paged_results) do
    case :lists.split(per_page, paged_results) do
      {h, []} -> {h, false}
      {h, t} -> {h, true}
    end
  end

  defp total_entries(query) do
    total_entries =
      query
      |> exclude(:preload)
      |> exclude(:select)
      |> subquery()
      |> select(count("*"))
  end
end

and my query:

@spec meetings_for_page_query() :: Ecto.Query.t()
def meetings_for_page_query() do
  from(m in Meeting,
    join: s in assoc(m, :speakers),
    where: m.date < ^DateTime.utc_now(),
    order_by: [desc: m.date],
    preload: [speakers: s]
  )
end