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?