Trouble with binding when composing Ecto.Query

Hi,

I am trying to compose an Ecto.Query but I am having trouble with the bindings.

I am searching a table of Notes. Notes as a many_to_many relationship with both Players and Categories.

The User is able to create a search that returns Notes that have one or more players and one or more categories.

I have a list of search terms that I collect from the frontend which I turn into an ordered list of filter, id and cond:

[{"1", {"player", "1", "AND"}}, {"2", {"category", "5", "AND"}}]

To test composing the query I have created the following actions:

defp create_query(list, short_name) do
    base_query()
    |> compose_query(list,"AND")
    |> Repo.all(prefix: "test")
  end

  defp base_query() do
    from n in Note
  end
  
  defp compose_query(query, [], _condition), do: query
  defp compose_query(query, [{_no, {filter, term, new_condition}} | search_terms], _condition) do
    case filter do
      "player" ->
        query
        |> join(:inner, [n], np in NotePlayers, on: [note_id: n.id])
        |> join(:inner, [n, np], p in Player, on: [id: np.player_id])
        |> where([n, np, p], p.id == ^term) 
        |> compose_query(search_terms, new_condition)
      "category" ->
        query 
        |> join(:inner, [n], nc in NoteCategory, on: [note_id: n.id]) # or use a schema for user_roles
        |> join(:inner, [n, nc], l in LookupNoteCategory, on: [id: nc.lookup_note_category_id]) 
        |> where([n, nc, l], l.id == ^term)
        |> compose_query(search_terms, new_condition)
    end  
    
  end

At this stage I am ignoring the condition as I can’t get this to work. The query that is created is:

SELECT n0."id", n0."post", n0."tenant_user_id", n0."inserted_at", n0."updated_at" FROM "test"."notes" AS n0
INNER JOIN "test"."note_players" AS n1 ON n1."note_id" = n0."id" 
INNER JOIN "test"."players" AS p2 ON p2."id" = n1."player_id" 
INNER JOIN "test"."note_categories" AS n3 ON n3."note_id" = n0."id" 
INNER JOIN "test"."lookup_note_categories" AS l4 ON l4."id" = n1."lookup_note_category_id" WHERE (p2."id" = $1) AND (p2."id" = $2)

    hint: Perhaps you meant to reference the column "n3.lookup_note_category_id".

The hint below the query is correct, if I change the query so the category is matched on the join table lookup_note_category_id it works.

What I can’t work out is why it is binding to n1 when I generate the query. Any help will be greatly appreciated.

Andrew