Trying to order by distance using an input geometry
t(x), while comparing to geometries from a JOINed table.
WITH "nearest_groups" as ( SELECT groups.id, ST_Distance(t.x, locations.center) AS nearest FROM (SELECT ST_GeographyFromText('SRID=4326;POINT(-121.0611 39.2191)') ) AS t(x), groups INNER JOIN locations ON groups.location_id = locations.id WHERE ST_DWithin(t.x, locations.center, 300000) ) SELECT * FROM "groups" INNER JOIN "nearest_groups" ON "groups"."id" = "nearest_groups"."id" ORDER BY "nearest_groups"."nearest" asc
Error: column "nearest_groups.nearest" must appear in the GROUP BY clause or be used in an aggregate function*
I don't understand what the error measure needs me to do to make this query work. Does it make sense to throw an GROUP BY in there? I'm not familiar with aggregate functions either.
(!!!) The query seems to work fine in PSQL but not in our ORM env (bookshelfjs/Knex). I find this alarming; ORMs give me the ever present fear that I will have to arm-wrestle them into doing what I want
UPDATE: We are using GraphQL and a common pattern is to fetch items that can be paged by tacking on a
hasMore boolean and
total count. SO
Somewhere else, 'hasMore' and 'total' are being compiled, and it is THERE that this error is being throw, since those are using an aggregate function