Use WITH ORDINALITY in Postgres for user defined sort order

Ash Forum Ash Questions / Help
,
1

Greetings all!

I would like to implement user defined sorting in my Ash application. When I did this in the past I would use a query like the following in postgres:

SELECT * FROM list_item 
JOIN unnest('{1,2,3}'::int[]) 
WITH ORDINALITY t(id, ord) USING (id) 
ORDER BY t.ord

Now to implement this in an Ash application I came up with the idea to define an array attribute of type uuid on my “list” resource which would contain the order of the “list_item” IDs that should be in the unnest call in the query above. “list” and “list_item” are in a one_to_many relationship.

I’m a bit confused though where the above query would fit into the framework. Would that be a manual relationship or something else entirely? Maybe there is even a better way of doing what I want to achieve that I’m unaware of.

Thank you for your help!

2

This is an interesting use case :slight_smile: 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.

3

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.

4

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: Join Manual Relationships — ash_postgres v1.4.0

5

I see, thank you very much for the pointers! :slight_smile: