Trouble with binding when composing Ecto.Query

Questions / Help
1

Hi,

I am trying to compose anEcto.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

2

So I found the answer … weirdly it was in the docs! :slight_smile:

because the binding had an unknown number before hand you can use ... to ensure you get the last bind. So I changed the code to:

join(:inner, [n, ..., nc], l in LookupNoteCategory, on: [id: nc.lookup_note_category_id]) 
where([n, ..., l], l.id == ^term)
3 Likes
3

If you’re using ecto 3 I’d also suggest looking into named bindings. Those make composability of queries quite a bit more comfortable.

5 Likes
4

Yes this, I cannot recommend them enough!