How do I avoid an 'unbound variable' error when the query is unknown at compile but known when the function would be run?

Question

I currently have a query going on the following journey:

# Context
def list_customers(params) do
 items = 
    from(i in MyAppItems)
    |> MyappItems.filter_by_params(params |> Enum.to_list())

    MyAppCustomer
    |> join(:left, [p], i in ^items, on: [customer_id: p.id])
    |> join(:left, [_, i], pr in assoc(i, :provider))
    |> join(:left, [_, i, _], t in assoc(i, :type))
    |> join(:left, [_, i, _, _], s in assoc(i, :status))
    |> join(:left, [_, i, _, _, _], a in assoc(i, :action))
    |> join(:left, [_, i, _, _, _, _], n in assoc(i, :note))
    |> preload([_, i, pr, t, s, a, n],
      items: {i, provider: pr, type: t, status: s, action: a, note: n}
    )
    |> group_by([p, _, _, _, _, _, _], p.id)
    |> Repo.all()
end

# MyAppItems
  def filter_by_params(query, params) do
    Enum.reduce(params, query, fn
      {"list_date", list_date}, query ->
        filter_by_list_date(query, list_date)
     _, query ->
        query
    end)
  end

  defp filter_by_list_date(query, list_date) do
    {:ok, date} = Date.from_iso8601(list_date)

    query
    |> where(fragment("date(inserted_at) = ?", ^date))
  end

As is, when this runs I get an ambiguous column warning with regards to inserted_at.

I tried to fix this by changing the fragment as follows:

|> where(fragment("date(?) = ?", i.inserted_at, ^date))

However I can't shake unbound_variable errors surrounding the i.. I know that when the query runs i will be in the query that is being passed to the fragment but I can't get to that point because of the compile error.

Answer 1

You can alias your joins to reference in a later piped chain.

For example:

 MyAppCustomer
 |> join(:left, [p], i in ^items, on: [customer_id: p.id], as: :item)
 |> where([item: item], fragment("date(?) =?", item.inserted_at, ^date))

Alternatively, if you know your hardcoded joins, you can do the same thing you were doing with the joins

MyAppCustomer
|> join(:left, [p], i in ^items, on: [customer_id: p.id], as: :item)
|> where([_, i],  fragment("date(?) =?", i.inserted_at, ^date))