Wait, did we add this feature?! :D

Ok, so I double checked and this feature does not work. It works for subqueries exclusively but it means we shouldn't document it. Of course we could make it more general purpose and have this case work but we would need to address that first. :)

I'm confused. 😄 I have a working demo at https://github.com/nathanl/people_query_demo

The relevant code is:

today = Date.utc_today()

base_query =
  from(person in Person,
    select: %{
      name: person.name,
      lifespan:
        fragment(
          # If person is alive, calculate lifespan until today
          "COALESCE(?, ?) - ? AS lifespan",
          person.death,
          ^today,
          person.birth
        )
    }
  )

base_query
|> order_by([{:asc, fragment("lifespan")}])
|> People.Repo.all()
|> IO.inspect(label: "ascending by lifespan ")

base_query
|> order_by([{:desc, fragment("lifespan")}])
|> People.Repo.all()
|> IO.inspect(label: "descending by lifespan ")

which logs:

[debug] QUERY OK source="people" db=1.0ms decode=0.6ms queue=31.4ms idle=0.0ms
SELECT p0."name", COALESCE(p0."death", $1) - p0."birth" AS lifespan FROM "people" AS p0 ORDER BY lifespan [~D[2022-02-02]]
ascending by lifespan : [
  %{lifespan: 763, name: "C"},
  %{lifespan: 25780, name: "B"},
  %{lifespan: 27545, name: "A"}
]
descending by lifespan : [
  %{lifespan: 27545, name: "A"},
  %{lifespan: 25780, name: "B"},
  %{lifespan: 763, name: "C"}
]
[debug] QUERY OK source="people" db=0.9ms queue=1.2ms idle=18.7ms
SELECT p0."name", COALESCE(p0."death", $1) - p0."birth" AS lifespan FROM "people" AS p0 ORDER BY lifespan DESC [~D[2022-02-02]]

Were you saying that ⬆️ is not supported, or am I misunderstanding?

Oh, I am so sorry. I thought the name was coming from the map key! But it is coming from the fragment. Doh!

💚 💙 💜 💛 ❤️

Hmmm. If the example confused you, that's not good 😬 I'll do a follow-up PR to make it clearer.

@nathanl it confused me because I know how the internals work, it is definitely on me, but clarifications are always welcome!