how to order and group by count subquery from an ordered list on ruby on rails

Question

I am trying to do this on Ruby On Rails with Postgres PostGIS.

I am trying to do a grouping and count on city of the ordered list I have.

I have an ordered list of nearest points to my coordinates(110.1862202 1.6031959).

Now I want to group it by cities and count the number of items in each cities. However, the format is correct but somehow it has lost its ordering. I would like the cities are ordered nearest to my coordinate. The whole point is to get the nearest city but now its not ordered nearest.

query problem group and count subquery

  scope :nearestcitysubquery, -> () {
    from(Treasure.order("ST_Distance(treasures.location_point, 
         ST_GeographyFromText('SRID=4326;POINT(110.1862202 1.6031959)'))"),
         "subquery_treasure_nearest").group("city").count
  }

actual outcome

=> {"Kuala Lumpur"=>1, "null"=>1, "Sungai Besar"=>1, "Sungai Udang"=>1, "Kuching"=>1}

expected outcome

=> {"Kuching"=>1, "Sungai Besar"=>1, "Sungai Udang"=>1, "Kuala Lumpur"=>1, "null"=>1}

My ordering query works correctly

Treasure.order("ST_Distance(treasures.location_point, 
         ST_GeographyFromText('SRID=4326;POINT(110.1862202 1.6031959)'))")

result of the ordering (which is correct)

=> #<ActiveRecord::Relation [#<Treasure id: 5, user_id: 1, treasure_name: "Kucing", treasure_image: nil, description: "Kucing", hint: nil, location: "Jalan Ke Puncak Serapi", created_at: "2016-01-08 03:46:40", updated_at: "2016-01-08 03:46:40", treasure_id: "TRKKY", location_point: #<RGeo::Geographic::SphericalPointImpl:0x3fe9942b71c8 "POINT (110.18 1.6)">, city: "Kuching", state: "Sarawak", country: "Malaysia", difficulty: 2, google_place_id: "ChIJZf_iRaYF-zERMH5qt30cJJw", size: 2>, #<Treasure id: 4, user_id: 1, treasure_name: "Lori", treasure_image: nil, description: "Lori", hint: nil, location: "Jalan Samsudin", created_at: "2016-01-08 03:45:26", updated_at: "2016-01-08 03:45:26", treasure_id: "T3UKD", location_point: #<RGeo::Geographic::SphericalPointImpl:0x3fe9942bbd68 "POINT (102.14 2.27)">, city: "Sungai Udang", state: "Melaka", country: "Malaysia", difficulty: 2, google_place_id: "ChIJq6_zCZX50TERBdlr6V1CMDg", size: 2>, #<Treasure id: 3, user_id: 1, treasure_name: "Kapal", treasure_image: nil, description: "Kapal", hint: nil, location: "Unnamed Road", created_at: "2016-01-07 09:21:07", updated_at: "2016-01-07 09:21:07", treasure_id: "T3XQR", location_point: #<RGeo::Geographic::SphericalPointImpl:0x3fe9942ba29c "POINT (101.76 3.5)">, city: "null", state: "Selangor", country: "Malaysia", difficulty: 2, google_place_id: "ChIJWU_xuCoTzDERsigXByaXof4", size: 3>, #<Treasure id: 1, user_id: 1, treasure_name: "Kelapa", treasure_image: nil, description: "Food", hint: nil, location: "Jalan Kiara 3", created_at: "2016-01-07 06:08:56", updated_at: "2016-01-07 06:08:56", treasure_id: "TJKQL", location_point: #<RGeo::Geographic::SphericalPointImpl:0x3fe9943647d8 "POINT (101.65 3.17)">, city: "Kuala Lumpur", state: "Wilayah Persekutuan Kuala Lumpur", country: "Malaysia", difficulty: 2, google_place_id: "ChIJ8fBgm-1IzDERPHAF2dYtWTc", size: 2>, #<Treasure id: 2, user_id: 1, treasure_name: "Pulau", treasure_image: nil, description: "Pulau", hint: nil, location: "Jalan Parit 4 Barat", created_at: "2016-01-07 09:20:47", updated_at: "2016-01-07 09:20:47", treasure_id: "TO1BR", location_point: #<RGeo::Geographic::SphericalPointImpl:0x3fe9942bd744 "POINT (101.0 3.7)">, city: "Sungai Besar", state: "Selangor", country: "Malaysia", difficulty: 2, google_place_id: "ChIJAfwEx2lgyzERU_zVy8TqaW0", size: 3>]>

NOTE: I was told subquery does not order, probably an outer join may work. How to create such a query ?

Answer 1

There are actually a few things going on here which are preventing your ordering from working.

• First, like you mentioned, any ORDER clause in a subquery -- which is what your location query is in -- is going to be irrelevant for the order of the final result set.

• Secondly, your GROUP BY city clause makes it logically impossible to order your results unambiguously. Suppose you have treasure A in city X, distance=10, treasure B in city Y, distance=15, and treasure C in city X, distance=20. Would City X come first or last in your results?

  However, I think it's reasonable to assume all cities will contain treasures in roughly a similar area, so it's reasonable to order by the closest treasure. So we can handle that problem.

• Lastly, Rails' group().count currently outputs a hash of values to counts. That API is probably fairly new and likely to change (hashes really shouldn't be ordered, in my opinion, and the format isn't very extensible). So relying on it might be a bad idea. But, from my testing, it does spit out results in the requested order.

In short, what we need to do is move the order to the top level of your query. To do that, the order clause needs to use an aggregate function (I'll pick MIN, to order by the nearest treasure), and we can drop the fancy .from code:

# I'm assuming this is on the Treasure model
scope :nearestcitysubquery, -> () {
  group('city')
    .order("MIN(ST_Distance(treasures.location_point, 
       ST_GeographyFromText('SRID=4326;POINT(110.1862202 1.6031959)')))")
    .count
}