Sorting by a calculation that performs a subquery raises an error

[sevenseacat]

eg.

Artist resource has a calculation followed?, defined like:

calculate :followed?, :boolean, expr(exists(artist_followers, follower_id == ^actor(:id)))

Attempting to sort a query reading artists by this calculation, like:

read :search do
  prepare fn query, _context ->
    Ash.Query.sort(query, followed?: :desc)
  end
end

Raises the following error:

[error] ** (ArgumentError) subqueries are not allowed in `order_by` expressions
    (ecto 3.11.2) lib/ecto/query/builder/dynamic.ex:72: Ecto.Query.Builder.Dynamic.partially_expand/5
    (ecto 3.11.2) lib/ecto/query/builder/order_by.ex:163: Ecto.Query.Builder.OrderBy.dynamic_or_field!/4
    (ecto 3.11.2) lib/ecto/query/builder/order_by.ex:142: anonymous fn/4 in Ecto.Query.Builder.OrderBy.order_by_or_distinct!/4
    (elixir 1.16.2) lib/enum.ex:1826: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (ecto 3.11.2) lib/ecto/query/builder/order_by.ex:140: Ecto.Query.Builder.OrderBy.order_by_or_distinct!/4
    (ecto 3.11.2) lib/ecto/query/builder/order_by.ex:157: Ecto.Query.Builder.OrderBy.order_by!/5
    (ash_sql 0.1.1-rc.12) lib/sort.ex:222: AshSql.Sort.sort/6
    ...

Notes

• if there is an exists in an order_by then we need to move the calculation into the select and then sort on the selected name

Runtime

• Elixir version 1.16.2-otp-26
• Erlang version 26.2.3
• Ash version 3.0.0-rc.31
• AshPostgres version 2.0.0-rc.11
• PostgreSQL version 15.5

[zachdaniel]

Interesting...so I was originally thinking that this was a postgres limitation, but upon further research it isn't. However, I'll need to see why Ecto doesn't allow this. If it is just work that hasn't been done then we'll fix it in ecto instead of patching it on our end by moving the value to the select and referencing that.

[zachdaniel]

Relevant mailing list conversation: https://groups.google.com/g/elixir-ecto/c/tgKNosEtyzI

[zachdaniel]

This will be resolved when this is merged elixir-ecto/ecto#4417 (or something like it is merged). I'm going to close this issue for now, and will track the issue in the ecto issue. Updating ecto at some point will resolve the issue.

[sevenseacat]

Thanks Zach! That'll be a killer feature 💪