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.