Subqueries in order_by are not allowed

[Zach Daniel]

This limitation affects ash query building as noted in this issue: https://github.com/ash-project/ash_postgres/issues/262. No need to look at that as its Ash-specific, just putting it here for reference.

In Ash, this calculation:

```elixir

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

```

will translate to a roughly equivalent ecto query, i.e

`exists(subquery(form row in "related", ....))`

Additionally, we allow folks to reference calculations in filters and sorts, but therein lies the problem. If someone tries to do

`Ash.Query.sort(query, [:followed?])`, we will get an error from ecto

[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 ...

My question is:

Is there a reason for this limitation? As far as I can tell, postgres will let you use a subquery in an order by expression. Is it just work that hasn't been done yet? If that is the case, I will happily PR that functionality to Ecto :) I wanted to ask here before logging a bug or trying to submit a PR though because often when Ecto doesn't let you do something its for a good reason 😀

Thanks all!

[José Valim]

If there is a limitation, it would be technical (I.e. complexity of the implementation). But we support them in where, so the initial work to also support them in order by is likely done.

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/6982ea6f-066e-46e6-9d59-bd166a0108d5n%40googlegroups.com.

[Zachary Daniel]

Makes sense 👍 I will explore an implementation in expo to resolve this ash issue in that case 🎉

On May 3, 2024, at 2:10 AM, José Valim <jose....@dashbit.co> wrote:



You received this message because you are subscribed to a topic in the Google Groups "elixir-ecto" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/elixir-ecto/tgKNosEtyzI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAGnRm4Lsb_pm0%2BKNg9N1_8odzDW6WuAt4So42LfisRnUZWjGxw%40mail.gmail.com.