Ecto query with joins and preload is generating weird SQL

Questions / Help
,
1

Hello :wave:

I query “questions” from the database, a “question” is part of a “collection” and has multiple possible “answers” (multi-choice test).

query =
  Question
  |> join(:inner, [q], a in assoc(q, :answers))
  |> preload([q, a], answers: a)
  |> join(:inner, [q], c in assoc(q, :collection))
  |> preload([q, c], collection: c)

IO.inspect Ecto.Adapters.SQL.to_sql(:all, Repo, query)

The above query generates the following SQL:

{“SELECT q0.“id”, q0.“title”, q0.“slug”, q0.“text”, q0.“media”, q0.“answer_note”, q0.“type”, q0.“short_id”, q0.“lesson_id”, q0.“collection_id”, q0.“canonical_question_id”, q0.“inserted_at”, q0.“updated_at”,
a1.“id”, a1.“correct”, a1.“text”, a1.“media”, a1.“input_text”, a1.“input_attrs”, a1.“order”, a1.“question_id”, a1.“inserted_at”, a1.“updated_at”,
a1.“id”, a1.“correct”, a1.“text”, a1.“media”, a1.“input_text”, a1.“input_attrs”, a1.“order”, a1.“question_id”, a1.“inserted_at”, a1."updated_at"
FROM “questions” AS q0 INNER JOIN “answers” AS a1 ON a1.“question_id” = q0.“id” INNER JOIN “collections” AS c2 ON c2.“id” = q0.“collection_id””,
[]}

I have put in bold what I think is weird. The select fields of “answers” are being duplicated; actually the query is more complex than that and the answers’ select field appear many times in the generated query.

Any idea why it’s that way? Is that a bug? Did I do something wrong?

2

:wave:

Try

query =
  Question
  |> join(:inner, [q], a in assoc(q, :answers))
  |> preload([q, a], answers: a)
  |> join(:inner, [q], c in assoc(q, :collection))
  |> preload([q, _, c], collection: c) # <---

or

query =
  Question
  |> join(:inner, [q], a in assoc(q, :answers))
  |> join(:inner, [q], c in assoc(q, :collection))
  |> preload([q, a, c], answers: a, collection: c) # <---
2 Likes
3

How did you come up with that?

4

https://hexdocs.pm/ecto/3.2.5/Ecto.Query.html#module-positional-bindings

3 Likes
5

Thank you!

6

I guess you could then do the following?

preload([_, _, c], collection: c)

Why ignore the “a” binding and not the “q”?