Error upgrading from 1.0.7 -> 1.0.8: cannot preload associations in subquery in query
Closed this issue · 4 comments
Upgraded to 1.0.8 of dataloader and am getting this error now:
** (Dataloader.GetError) {%Ecto.SubQueryError{exception: %Ecto.QueryError{message: "cannot preload associations in subquery in query:\n\nfrom u0 in Server.Accounts.User,\n join: u1 in Server.Accounts.UserProfile,\n on: u1.user_id == u0.id,\n where: u0.id == parent_as(:input).id,\n limit: ^40,\n offset: ^0,\n preload: [profile: u1]\n"}, message: "the following exception happened when compiling a subquery.\n\n ** (Ecto.QueryError) cannot preload associations in subquery in query:\n \n from u0 in Server.Accounts.User,\n join: u1 in Server.Accounts.UserProfile,\n on: u1.user_id == u0.id,\n where: u0.id == parent_as(:input).id,\n limit: ^40,\n offset: ^0,\n preload: [profile: u1]\n \n\nThe subquery originated from the following query:\n\nfrom u0 in subquery(from u0 in Server.Accounts.User,\n where: u0.id in ^[\"4cb62820-1d0e-4265-852a-e62086d98264\"],\n distinct: true,\n select: [:id]),\n as: :input,\n join_lateral: u1 in subquery(from u0 in Server.Accounts.User,\n join: p1 in assoc(u0, :profile),\n where: u0.id == parent_as(:input).id,\n limit: ^40,\n offset: ^0,\n preload: [profile: p1]),\n on: true,\n select: u1\n"}, [{Ecto.Repo.Queryable, :execute, 4, [file: 'lib/ecto/repo/queryable.ex', line: 176]}, {Ecto.Repo.Queryable, :all, 3, [file: 'lib/ecto/repo/queryable.ex', line: 17]}, {Dataloader.Ecto, :run_batch, 6, [file: 'lib/dataloader/ecto.ex', line: 328]}, {Dataloader.Source.Dataloader.Ecto, :run_batch, 2, [file: 'lib/dataloader/ecto.ex', line: 643]}, {Dataloader.Source.Dataloader.Ecto, :"-run_batches/1-fun-1-", 2, [file: 'lib/dataloader/ecto.ex', line: 601]}, {Task.Supervised, :invoke_mfa, 2, [file: 'lib/task/supervised.ex', line: 90]}, {Task.Supervised, :reply, 5, [file: 'lib/task/supervised.ex', line: 35]}, {:proc_lib, :init_p_do_apply, 3, [file: 'proc_lib.erl', line: 226]}]} (dataloader 1.0.8) lib/dataloader.ex:223: Dataloader.do_get/2
Didn't see anything mentioned in the changelog about this - happy to dig in and provide more information if helpful!
Hey @davidye can you provide more info? Can you show your loader file?
@benwilson512 I have schemas User and and UserProfile and querying for user, I preload UserProfile.
So in my resolver:
def add_source(loader, session) do
loader
|> Dataloader.add_source(
Accounts,
Dataloader.Ecto.new(
Repo,
query: fn _query, params ->
{_params_session, args} = Map.split(params, [:session])
Accounts.query(args, session)
end,
default_params: %{session: session}
)
)
end
def user_profile(%User{id: user_id}, _args, %{context: %{loader: loader}}, fun \\ nil) do
loader
|> Dataloader.load(Accounts, {User, load: :profile}, user_id)
|> on_load(fn loader ->
loader
|> Dataloader.get(Accounts, {User, load: :profile}, user_id)
|> case do
%User{} = user -> {:ok, user.profile}
nil -> {:error, :not_found}
end
|> default_fun(fun)
end)
end
So dataloader calls Accounts.query(...) with [load: :profile] and that constructs a standard Ecto query of this form:
from user in query,
join: profile in assoc(user, :profile),
preload: [profile: profile]
Also just noticed there's more error output - did something change with the way dataloader constructs ecto queries in 1.0.8?
11:11:29.346 [error] Task #PID<0.3055.0> started from #PID<0.3058.0> terminating
** (Ecto.SubQueryError) the following exception happened when compiling a subquery.
** (Ecto.QueryError) cannot preload associations in subquery in query:
from u0 in Server.Accounts.User,
join: u1 in Server.Accounts.UserProfile,
on: u1.user_id == u0.id,
where: u0.id == parent_as(:input).id,
limit: ^40,
offset: ^0,
preload: [profile: u1]
The subquery originated from the following query:
from u0 in subquery(from u0 in Server.Accounts.User,
where: u0.id in ^["18dd4bf9-92ff-4448-9631-b6031355ce32"],
distinct: true,
select: [:id]),
as: :input,
join_lateral: u1 in subquery(from u0 in Server.Accounts.User,
join: p1 in assoc(u0, :profile),
where: u0.id == parent_as(:input).id,
limit: ^40,
offset: ^0,
preload: [profile: p1]),
on: true,
select: u1
EDIT: Looks like there's been some changes to preload behavior in lib/dataloader/ecto.ex which maybe related? @mbuhot
@davidye yes the presence of the limit/offset in the query is causing it to use a new code path that runs your query as a lateral subquery.
It's intended to support getting the first N associated rows per parent record, but looks like I didn't test it with preloads, 🤦♂️.
To fix this specific issue we could update the code to not use the load_rows_lateral
function when loading a batch of rows using the primary key. load_rows_lateral
should only be used when loading an association by a foreign key.
I'll have to do some experimenting to figure out how to support preloads generally.
In some cases we could replace a preload in the subquery with a preload in the outer query, or a call to Repo.preload
However that won't work for join preloads 🤔
Fixed in #98