Solution Edit: While preparing this question I’ve think I’ve discovered my actual issue. Originally I was just using a join:
as opposed to a left_join:
. I assumed it was limiting by the associations because it would return the correct data until you added speakers. I wasn’t using a distinct:
parameter at the time either, returning a data set that was really hard to recognize a pattern within (at the time). It seems giving a distinct: true
(which I wasn’t aware you could do) parameter as opposed to distinct: m.date
returns the desired output when pass through pagination. I was preparing example data for context and my tests were… passing.
Old problem:
I’ve found a workaround but it requires an extra query to hit the database which I’d like to avoid if possible. In my experience, taking the limit
/offset
of a query with a preloaded association will return undesired results.
Say I have this schema:
schema "meetings" do
field(:date, :utc_datetime)
has_many(:speakers, Speaker)
has_many(:attendees, Attendee)
timestamps()
end
and this 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]
)
end
passing it through:
defp paged_query(query, page, per_page) do
offset = per_page * (page - 1)
query
|> limit(^(per_page + 1))
|> offset(^offset)
end
will return a result where I believe it is including the preloaded associations as part of the limit.
My current solution is just to preload the associations after pagination, retrieving and replacing the map from the paginated results like so:
def index(conn, params) do
page = params["page"] || 1
per_page = params["per_page"] || 5
paged_result = Pagination.paginate(Meetings.meetings_for_page_query(), page, per_page)
preloaded_results =
Map.get(paged_result, :results)
|> Repo.preload(:speakers)
paged_result_w_preload =
paged_result
|> Map.put(:results, preloaded_results)
render(conn, "index.html", paged_result: paged_result)
end
I’ll be giving a short talk around a feature I added to a local Elixir meetup group’s website and wanted to see if anyone had any other solutions that I could add in and show different ways to approach the problem. Even a popular Elixir pagination library Scrivener has had this issue open for over a year (though I haven’t tried using their library to see if they’ve since updated it and just haven’t cleared the issue).
If anyone is interested, here is the powerpoint I’ll be showing. If you have any suggestions please feel free!