So I have this run-of-the-mill data table with some filtering options, and I’m trying to use dynamic queries to filter it, but I can’t figure out how to get Ecto to use the right table placeholders:
The query:
def list_company_onboarding_status(sort_direction, sort_field, where_params) do
Repo.all(
from os in OnboardingStatus,
join: c in TorskCompany,
on: os.company_id == c.id,
select: {os, c},
where: ^company_onboarding_status_where_query(where_params),
order_by: [{^sort_direction, ^sort_field}]
)
end
and the dynamic where query generator:
defp company_onboarding_status_where_query(params) do
Enum.reduce(params, dynamic(true), fn
{:funnel_step, step}, dynamic ->
dynamic([os], ^dynamic and os.current_funnel_step == ^step)
{:migration_cluster, nil}, dynamic ->
dynamic([c], ^dynamic and is_nil(c.migration_cluster))
{:migration_cluster, value}, dynamic ->
dynamic([c], ^dynamic and c.migration_cluster == ^value)
{:migration_charge, nil}, dynamic ->
dynamic([c], ^dynamic and is_nil(c.migration_charge))
{:migration_charge, value}, dynamic ->
dynamic([c], ^dynamic and c.migration_charge == ^value)
{_, _}, dynamic ->
# Not a where parameter
dynamic
end)
end
When I run this, the dynamic where function generates something like dynamic([c], true and c.migration_cluster == ^99)
, but the query is generated with the wrong placeholder, and errors like this:
[error] GenServer #PID<0.1104.0> terminating
** (Ecto.QueryError) lib/marsvin/companies.ex:254: field `migration_cluster` in `where` does not exist in schema Marsvin.Companies.OnboardingStatus in query:
from o0 in Marsvin.Companies.OnboardingStatus,
join: t1 in Marsvin.Companies.TorskCompany,
on: o0.company_id == t1.id,
where: true and o0.migration_cluster == ^99,
order_by: [desc_nulls_last: o0.newest_change_at],
select: {o0, t1}
(elixir 1.11.3) lib/enum.ex:2193: Enum."-reduce/3-lists^foldl/2-0-"/3
The problem is that despite my dynamic part using the c
prefix for the where, indicating that it’s a column from the TorskCompany table, not the OnboardingStatus table, the generated query uses the o0
prefix for that column instead of the correct t1
, causing the error since it’s looking at the wrong table.
How do I get Ecto to generate the query correctly?