martinm Posts - Answer Overflow

martinm

•Created by martinm on 8/12/2024 in #help

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.

6 replies

Gaming

Programming