Postgrex - query returning an ambiguous column reference error

Question

I have the following queries:

def user_contacts(provider_id, filter) do
  query =
    from(
      u in User,
      preload: [:groups],
      where: u.provider_id == ^provider_id and u.type != "company"
    )

  query
  |> filter_sector(filter)
  |> filter_availability(filter)
end

defp filter_sector(query, %{"sector" => "Asset Management & Investment Funds"}) do
  query
  |> join(:inner, [u], p in Profile, p.user_id == u.id)
  |> where([u, p], fragment("(sectors->>'asset' = ?)", "true"))
end

defp filter_sector(query, _), do: query

defp filter_availability(query, %{"availability" => "now"}) do
  query
  |> join(:inner, [u], p in Profile, p.user_id == u.id)
  |> where([u, p], ^Date.utc_today >= p.placement_availability)
end

defp filter_availability(query, _), do: query

I am getting the following error: ERROR 42702 (ambiguous_column): column reference "sectors" is ambiguous. sectors is an embedded schema in Profile.

My attempt to fix this is by reworking the structure of the filter_sector query into this:

from(
  q in query,
  join: p in Profile,
  on: p.user_id == q.id,
  where: fragment("(sectors->>'asset' = ?)", "true")
)

but I am still getting the same error. What's odd is when the filter only has a "sector" value or an "availability" value the query works, but when both values are present the error comes up.

score 1 · Accepted Answer · answered May 21 '19 at 08:56

Without looking at the whole query, I can only speculate, but you likely have another table in that join with a sectors field on it. Or perhaps profiles are being joined twice. If you include the full error message, with stacktrace, as well as the generated query, it will help confirm this suspicion.

If this is the case, you can fix it by fully qualifying the sectors field, which is the preferred way to write queries:

query
|> join(:inner, [u], p in Profile, p.user_id == u.id)
|> where([u, p], fragment("(?->>'asset' = ?)", p.sectors, "true"))

Now there should be no ambiguity.

Postgrex - query returning an ambiguous column reference error

1 Answers1