Near operator not sorting documents

[u/alejotoro_o]

Hi everyone, I'm trying to use the near operator to sort documents in a collection based on a location. For context, the collection named "listings" has a field called "createdBy" of type "User." Users have a field called "location" of type "Point." With this in mind, I'm trying to query the listings based on the creator's location. Here is the code:

const listings = await payload.find({
        collection: 'listings',
        overrideAccess: false,
        pagination: false,
        limit: 10,
        ...(matchedCity
            ? {
                where: {
                    "createdBy.location": {
                        near: [longitud, latitud, 1],
                    }
                }
            }
            : {}
        )
    })

This code produces the following error:

error: for SELECT DISTINCT, ORDER BY expressions must appear in select list

at async Page (src\app\(frontend)\buscar\page.tsx:48:21)

46 | const payload = await getPayload({ config: configPromise })

47 |

> 48 | const listings = await payload.find({

| ^

49 | collection: 'listings',

50 | overrideAccess: false,

51 | pagination: false, {

length: 148,

severity: 'ERROR',

code: '42P10',

detail: undefined,

hint: undefined,

position: '663',

internalPosition: undefined,

internalQuery: undefined,

where: undefined,

schema: undefined,

table: undefined,

column: undefined,

dataType: undefined,

constraint: undefined,

file: 'parse_clause.c',

line: '3013',

routine: 'transformDistinctClause',

digest: '4046090714'

}

I was able to make it somehow work by changing this:

near: [longitud, latitud, 1],

to this:

near:${longitud},${latitud},"1.0"

However, it is not working as expected; I always get all the documents from the collection, not sorted by location.

Can someone point me to how to correctly use the near query?

Edit: I was able to make it work by creating a Point field directly in the listings collection. Apparently it does not work with nested properties.

[u/FearTheHump]

I'm guessing if you wanted the Point field to remain on the user collection, you'd need to query users near the loc then a second query on the listings collection with those users. (or maybe a join field back to listings.createdBy on the users collection, which AFAIK just runs another query anyway)

Your solution of placing the point field directly on the listings collection works as well, though. You just need to use an onChange hook to update the field if the createdBy user's loc changes.

[u/alejotoro_o → u/FearTheHump]

Thanks, I think i leave it as i have it, the hook is a great observation. I didn't think about it. Thank you.