AE
Ash Elixir•2y ago
hassaan_ayyub

How to sort on a postgres regex?

I'm trying to extract a number from a text field using regex and sort on it. 
require Ash.Query

def sort_on_regex(q) do
  q
  |> Ash.Query.sort([asc: expr(fragment( ... ))])
end

error: undefined function expr/1
error: undefined function fragment/1
require Ash.Query

def sort_on_regex(q) do
  q
  |> Ash.Query.sort([asc: expr(fragment( ... ))])
end

error: undefined function expr/1
error: undefined function fragment/1
Perhaps using calculations: 
calculate :serial_no_int, :integer, expr( ... )
calculate :serial_no_int, :integer, expr( ... )
I can't figure out the syntax. Thank you!
2 Replies
barnabasj
barnabasj•2y ago
A calculation should work. In the first code snippet, you probably need to import expr or do Ash.Query.expr and the fragment is from the AshPostgres Datalayer. Not sure which module from the top of my head. I haven't used sort that way though so not sure if it works, but I did something similar with calculations
hassaan_ayyub
hassaan_ayyubOP•2y ago
Update: 
calculate :serial_no_int, :integer, expr(fragment("cast(substring(serial_no from '\\d+$') as integer)"))

...

Ash.Query.sort(serial_no_int: {:asc, %{}})
calculate :serial_no_int, :integer, expr(fragment("cast(substring(serial_no from '\\d+$') as integer)"))

...

Ash.Query.sort(serial_no_int: {:asc, %{}})
works.

Did you find this page helpful?