1

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

3
  • You say the query works in psql. Does the ORM produce the same query or something else? Feb 10, 2022 at 19:43
  • The query can be much simpler if "groups".id is defined UNIQUE. (Sounds like the PK?) Please start your questions by declaring the RDBMS and version in use and core table definitions (CREATE TABLE statements) showing data types and constraints. Feb 10, 2022 at 20:29
  • I'd say that this error message is from a different query, one with GROUP BY in it. Feb 11, 2022 at 8:31

1 Answer 1

2

The syntax of the query is generally correct.

However, GROUPS is a reserved word in standard SQL (at least in SQL:2016 and SQL:2011). But "non-reserved" in Postgres.

This might explain why the query works in psql (talking to a Postgres server!), but not in GraphQL.

Don't use reserved words as identifier. If you are stuck with your choice, you must always double-quote the name, like "groups".

Aside 1

Postgres reduced the number of reserved words in recent releases (and strives too keep the number to a minimum). But standard SQL is still in the habit of reserving (too) many words, and many RDBMS go along with that. For "portable" SQL code (it's never really portable), avoid any and all reserved words, even those accepted by Postgres.

Aside 2

Assuming "groups".id is defined UNIQUE NOT NULL (like the PK), the query can be much simpler and cheaper:

SELECT g.*, st_distance(t.x, l.center) AS nearest
FROM  (SELECT ST_GeographyFromText('SRID=4326;POINT(-121.0611 39.2191)')) AS t(x)
     , "groups"  g
JOIN   locations l ON g.location_id = l.id
WHERE  ST_DWithin(t.x, l.center, 300000)
ORDER  BY nearest;

Only one instance of "groups".id is in the result, which should be welcome side effect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.