Using Rails 4.2 and Postgres I've created the following query to give me a uniq list of bird ids in the Sightings table ordered by the number of sightings for each bird. Also note I'm using the Kaminari gem for pagination.
Sighting.select("bird_id, COUNT(sightings.id) as sightings_count").group(:bird_id).order('sightings_count DESC').page(1)
This works great returning the ActiveRecordRelation intended. The problem arises when i try to combine it with the geocoder gems .near method
Sighting.near([-31.0, 151.0], 1000, units: :km).select("bird_id, COUNT(sightings.id) as sightings_count").group(:bird_id).order('sightings_count DESC').page(1)
This generates the query and error
SELECT sightings.*, 6371.0 * 2 * ASIN(SQRT(POWER(SIN((-31.0 - sightings.lat) * PI() / 180 / 2), 2) + COS(-31.0 * PI() / 180) * COS(sightings.lat * PI() / 180) * POWER(SIN((151.0 - sightings.lng) * PI() / 180 / 2), 2))) AS distance, MOD(CAST((ATAN2( ((sightings.lng - 151.0) / 57.2957795), ((sightings.lat - -31.0) / 57.2957795)) * 57.2957795) + 360 AS decimal), 360) AS bearing, bird_id, COUNT(sightings.id) as sightings_count FROM "sightings" WHERE (sightings.lat BETWEEN -39.993216059187304 AND -22.006783940812696 AND sightings.lng BETWEEN 140.50821379697885 AND 161.49178620302115 AND (6371.0 * 2 * ASIN(SQRT(POWER(SIN((-31.0 - sightings.lat) * PI() / 180 / 2), 2) + COS(-31.0 * PI() / 180) * COS(sightings.lat * PI() / 180) * POWER(SIN((151.0 - sightings.lng) * PI() / 180 / 2), 2)))) BETWEEN 0.0 AND 1000) GROUP BY bird_id ORDER BY distance ASC, sightings_count DESC LIMIT 25 OFFSET 0
PG::GroupingError: ERROR: column "sightings.id" must appear in the GROUP BY clause or be used in an aggregate function
Adding id to the group by means the bird counts aren't correct and as far as I understood COUNT within select was an aggregate function which does include sightings.id.
How can I successfully combine the two?
Note: I did try the following but this returns a Hash rather than AR Relation.
Sighting.near([@lat, @lng], @range, units: :km, order:nil).group(:bird_id).order('count_id DESC').page(@page).count(:id)
Thanks for any help!!
Creating a custom near scope was the easiest work around as I wasn't using the bearing or distance attributes that get added to each record and hence the whole select sql generated by near I didn't need. Instead of select(options[:select]) I replaced it with the select I wanted.
scope :birds_by_sighting, lambda{ |location, *args|
latitude, longitude = Geocoder::Calculations.extract_coordinates(location)
if Geocoder::Calculations.coordinates_present?(latitude, longitude)
options = near_scope_options(latitude, longitude, *args)
select("bird_id, COUNT(sightings.id) as sightings_count").group(:bird_id).where(options[:conditions]).order('sightings_count DESC')
else
# If no lat/lon given we don't want any results, but we still
# need distance and bearing columns so you can add, for example:
# .order("distance")
select(select_clause(nil, null_value, null_value)).where(false_condition)
end
}