AF
Ash Framework•3mo ago
absowoot

Query load and sort

I have a table locations with a geometry field :geom (using ash_geo + geo_postgis). When a user lands on a page and geolocates, the list of locations should sort ASC based on the user location and show the distance away in miles. My current query is: 
query
|> Ash.Query.load(distance_miles: %{geolocation: geolocation})
|> Ash.Query.sort(distance_miles: {%{geolocation: geolocation}, :asc})
query
|> Ash.Query.load(distance_miles: %{geolocation: geolocation})
|> Ash.Query.sort(distance_miles: {%{geolocation: geolocation}, :asc})
and my calculation is: 
calculate :distance_miles, :float do
  argument :geolocation, :geometry

  calculation expr(^st_distance_in_meters(geom, ^arg(:geolocation)) / 1609.34)
end
calculate :distance_miles, :float do
  argument :geolocation, :geometry

  calculation expr(^st_distance_in_meters(geom, ^arg(:geolocation)) / 1609.34)
end
The calculation works as expected but sorting does not (I believe it was working in a previous version). What am I missing and/or can I just calculate distance_miles and just sort on that? IE: 
query
|> Ash.Query.load(distance_miles: %{geolocation: geolocation})
|> Ash.Query.sort(:distance_miles)
query
|> Ash.Query.load(distance_miles: %{geolocation: geolocation})
|> Ash.Query.sort(:distance_miles)
(I tried this but it also did not work).
Solution:
Aha I found the issue was a Enum.reduce flipping the order later on
Jump to solution
4 Replies
ZachDaniel
ZachDaniel•3mo ago
🤔 that looks right to me. In what way does it not work? (the first one, not the second one)
absowoot
absowootOP•3mo ago
The locations appear to be sorting DESC or not at all 
SELECT l0."id", l0."name", l0."state", l0."slug", l0."geom", l0."street1", l0."street2", l0."city", l0."postal_code", l0."country", l0."provider_id", ((ST_Distance(l0."geom"::geometry::geography,$1::geography))::decimal / $2::decimal)::float FROM "locations" AS l0 ORDER BY ((ST_Distance(l0."geom"::geometry::geography,$3::geography))::decimal / $4::decimal)::float [%Geo.Point{coordinates: {-77.56993, 38.3927}, srid: 4326, properties: %{}}, Decimal.new("1609.34"), %Geo.Point{coordinates: {-77.56993, 38.3927}, srid: 4326, properties: %{}}, Decimal.new("1609.34")]
SELECT l0."id", l0."name", l0."state", l0."slug", l0."geom", l0."street1", l0."street2", l0."city", l0."postal_code", l0."country", l0."provider_id", ((ST_Distance(l0."geom"::geometry::geography,$1::geography))::decimal / $2::decimal)::float FROM "locations" AS l0 ORDER BY ((ST_Distance(l0."geom"::geometry::geography,$3::geography))::decimal / $4::decimal)::float [%Geo.Point{coordinates: {-77.56993, 38.3927}, srid: 4326, properties: %{}}, Decimal.new("1609.34"), %Geo.Point{coordinates: {-77.56993, 38.3927}, srid: 4326, properties: %{}}, Decimal.new("1609.34")]
ZachDaniel
ZachDaniel•3mo ago
🤔 thats pretty strange... Well, that query seems to be sorting them? I think its the :float casting Can you change distance_miles to :decimal? It will change the result of your calculation though.
Solution
absowoot
absowoot•3mo ago
Aha I found the issue was a Enum.reduce flipping the order later on

Did you find this page helpful?