Ecto order_by with an optional joined record

Questions / Help
1

I’m trying to perform an order by where joined (left outer join) record might not exist with Ecto, but failing to do so.

For example, almost not-a-pseudocode:

from(p in Post,
  left_join: c in assoc(p, :comments),
  as: :comment
)
|> order_by([p, comment: comment], coalesce(comment.title, p.title))

Currently this solution does not work since comment binding might not exist for records where there are no comments associated with post.

How can this situation be handled with Ecto?

2

This shouldn’t be the case. Can you show the actual error?

3

Now that I tried to reproduce this problem I realized that the problem might be that there is also a subquery/1 in use, which means that the example above is more like this instead:

from(p in Post,
  left_join: c in assoc(p, :comments),
  as: :comment
)
|> subquery()
|> order_by([p, comment: comment], coalesce(comment.title, p.title))

In this case it makes totally sense that binding does not exist and I assume that I have to somehow select coalesce(comment.title, p.title) AS title within subquery and then just order by p.title. Am I right? How can this be done with Ecto?

4

You can use Ecto.Query.API — Ecto v3.12.1 to refer to a parent binding within a subquery.

5

Managed to solve it!

from(p in Post,
  left_join: c in assoc(p, :comments),
  as: :comment,
  select_merge: %{title: coalesce(c.title, p.title)}
)
|> order_by([p], p.title)

Thank you for your fast reply which helped me into right direction!

6

Thanks for the hint, but I guess for me this would not work since I wanted to reference a subquery binding from outer query which does not make even sense.