Selection from subquery using getTableColumns not functioning as expected

I'm using the latest drizzle with postgres-js and the postgis extension. I have a schema which defines users with a location column which can be null. In a query to find users nearby a specified location I want to first filter out users with null locations in a subquery, and then find nearby users. Here's how I do that right now:
// Subquery to filter out users with no location
const sq = db
.select()
.from(user)
.where(sql`${user.location} IS NOT NULL`)
.as("sq");

// Query to find users within the radius
const result = await db
.select({
...getTableColumns(user),
distance: sql`ST_Distance(${user.location}, ${sqlPoint})`,
})
.from(sq)
.where(sql`ST_DWithin(${user.location}, ${sqlPoint}, ${radius}, false)`)
.orderBy(sql`${user.location} <-> ${sqlPoint}`)
.execute();
// Subquery to filter out users with no location
const sq = db
.select()
.from(user)
.where(sql`${user.location} IS NOT NULL`)
.as("sq");

// Query to find users within the radius
const result = await db
.select({
...getTableColumns(user),
distance: sql`ST_Distance(${user.location}, ${sqlPoint})`,
})
.from(sq)
.where(sql`ST_DWithin(${user.location}, ${sqlPoint}, ${radius}, false)`)
.orderBy(sql`${user.location} <-> ${sqlPoint}`)
.execute();
When running this, I'm met with this error: Error: Your "id" field references a column "user"."id", but the table "user" is not part of the query! Did you forget to join it? This makes some sense to me as I am selecting from the subquery (not the user table), but I am trying to use the getTableColumns function to retrieve all the columns from the subquery (which should be the same as the user table) along with an extra column for distance. How can I select all the columns from the subquery including my extra column? Using getTableColumns(sq) is giving me type errors, so that doesn't seem to be the solution.
5 Replies
rphlmr ⚡
rphlmr ⚡4mo ago
👋 getTableColumns is for table, so, all columns will be scoped to the table (“table_name”.”column”). You have to do it manually in the select: id: sq.id, etc I’m not aware of a helper that can do that for sub queries but I guess there is one somewhere here or in GitHub. Will try to search
rphlmr ⚡
rphlmr ⚡4mo ago
GitHub
Feat: add getColumns function to utils by Angelelz · Pull Request...
close #1459 Added new getColumns function that accepts Table | View | Subquery and returns the columns (selected fields). Added deprecation note to getTableColumns. Added tests for all 3 dialects.
rphlmr ⚡
rphlmr ⚡4mo ago
GitHub
drizzle-orm-helpers/src/utilities.ts at 035ffd5af68ebc2773011445cf3...
Collection of unofficial helpers for Drizzle ORM. Contribute to iolyd/drizzle-orm-helpers development by creating an account on GitHub.
rphlmr ⚡
rphlmr ⚡4mo ago
you can use this lib (experimental) or just copy/paste the helper
martinm
martinmOP4mo ago
thanks for the help!
Want results from more Discord servers?
Add your server