Use WITH ORDINALITY in Postgres for user defined sort order

This is an interesting use case At the moment, to add that join statement your only option would be to use a relatively heavy handed escape hatch called modify_query, which allows you to modify the underlying data layer query. It would look something like this:

read :user_sorted do
  modify_query fn ash_query, ecto_query -> 
    ecto_query = from row in ecto_query,
     ...
  end
end

You can then follow this guide here on writing the relevant ecto query:

This is an example of a good reason to use an escape hatch like this, however what we would ideally be able to do is apply this to any given query, instead of having to having this modify_query tied to the action. This would be a case for supporting a hook on Ash.Query called modify_query, something like this:

Ash.Query.modify_query(query, fn query, ecto_query -> ... end)

so you can attach that conditionally in a global preparation, instead of having to do it in the action.

Thank you that worked very well!
Is it also possible to use modify_query in a manual relationship? It would feel more natural to me if I could load the ordered items directly from a “list” resource without going the additional step of loading it from the “list_item” resource.

In a manual relationship you’d need to apply this kind of logic yourself. AshPostgres gives you tools to define joinable manual relationships. I’m not sure if this is exactly what you’re after, but it may be what you’re looking for: https://hexdocs.pm/ash_postgres/join-manual-relationships.html

I see, thank you very much for the pointers!