Help with subquery including joins and preloads for pagination

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.

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

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

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