Ecto pipe syntax join not working as expected

Questions / Help
1

My join using the pipe syntax is not working as I expect.

data:

| id | parent_id |
| -- | --------- |
| 1  |           |
| 2  | 1         |
| 3  | 2         |

code:

defmodule Tst do
  use Ecto.Schema

  schema "tst" do
    belongs_to(:parent, Tst)
  end
end

from(Tst, as: :a)
|> join(:left, [a], assoc(a, :parent), as: :p)
|> join(:left, [p], assoc(p, :parent), as: :pp)
|> where([a], a.id == 3)
|> select([a, p, pp], {a.id, p.id, pp.id})
|> Repo.one()

This returns {3, 2, 2} instead of {3, 2, 1} as I expected. The generated query is:

SELECT t0."id", t1."id", t2."id"
FROM "tst" AS t0
LEFT OUTER JOIN "tst" AS t1 ON t1."id" = t0."parent_id"
LEFT OUTER JOIN "tst" AS t2 ON t2."id" = t0."parent_id"
WHERE (t0."id" = 3) ORDER BY t0."id"

Keyword join works as expected:

from(a in Tst,
  left_join: p in assoc(a, :parent),
  left_join: pp in assoc(p, :parent),
  where: a.id == 3,
  select: {a.id, p.id, pp.id}
)
|> Repo.one()

returns {3, 2, 1} from the generated query

SELECT t0."id", t1."id", t2."id"
FROM "tst" AS t0
LEFT OUTER JOIN "tst" AS t1 ON t1."id" = t0."parent_id"
LEFT OUTER JOIN "tst" AS t2 ON t2."id" = t1."parent_id"
WHERE (t0."id" = 3) ORDER BY t0."id"

What’s the problem here?

2

Can you change it to this to see if it works:

from(Tst, as: :a)
|> join(:left, [a: a], assoc(a, :parent), as: :p)
|> join(:left, [p: p], assoc(p, :parent), as: :pp)
|> where([a: a], a.id == 3)
|> select([a: a, p: p, pp: pp], {a.id, p.id, pp.id})
|> Repo.one()

See: Ecto.Query — Ecto v3.9.4

Edit: I think the issue is here in the ordering, is seems that in your case p is actually a because it’s the first in the list.

|> join(:left, [p], assoc(p, :parent), as: :pp)

vs

|> join(:left, [a, p], assoc(p, :parent), as: :pp)
2 Likes
3

The bindings in things like join are positional unless the keyword-list shape is used - this query will give equally-incorrect results:

# NOTE: this still generates the same wrong SQL!!!!
from(Tst, as: :a)
|> join(:left, [mumble], assoc(mumble, :parent), as: :p)
|> join(:left, [blargh], assoc(blargh, :parent), as: :pp)
|> where([a], a.id == 3)
|> select([a, p, pp], {a.id, p.id, pp.id})
|> Repo.one()

What you want is either to add an additional (unused) placeholder:

from(Tst, as: :a)
|> join(:left, [a], assoc(a, :parent), as: :p)
|> join(:left, [_a, p], assoc(p, :parent), as: :pp)
|> where([a], a.id == 3)
|> select([a, p, pp], {a.id, p.id, pp.id})
|> Repo.one()

or make use of the names added with as:

from(Tst, as: :a)
|> join(:left, [a: a], assoc(a, :parent), as: :p)
|> join(:left, [p: p], assoc(p, :parent), as: :pp)
|> where([a: a], a.id == 3)
|> select([a: a, p: p, pp: pp], {a.id, p.id, pp.id})
|> Repo.one()
4 Likes
4

Yes, this works, thank you!