[BUG]: `findFirst` and `findMany` isn't correctly setting the table name when using sql directive `sql`${table}``

[Odas0R]

What version of drizzle-orm are you using?

0.28.5

What version of drizzle-kit are you using?

0.19.13

Describe the Bug

      const storeRows = await this.db.db.query.store.findFirst({
        extras: {
          ratings: sql<number | null>`(
            select
             coalesce(round(avg(${storeRating.value}::double precision)::numeric, 1)::float, 0)
             from ${storeRating} where ${storeRating.storeId} = ${store.id}
          )
          `.as("ratings"),
        },  
       })      

The storeRating table name is store_rating, but what's being injected is the table name store, producing the error

error: PostgresError: column store.value does not exist

Expected behavior

sql directive should fetch the table name correctly

Environment & setup

NODE: v16.19.1 Pop!_OS 22.04 LTS

[Angelelz]

Can you create a quick reproduction for this? I'll investigate.

[jstlaurent]

@Angelelz : I have the same issue. Here's my current set up.

Database schema:

export const review = pgTable(
  'review',
  {
    // Internal
    id: varchar('id', { length: 256 }).$defaultFn(generateId).primaryKey(),
    createdAt: timestamp('created_at', {
        withTimezone: true,
        mode: 'date'
      })
        .notNull()
        .defaultNow(),
    deletedAt: timestamp('deleted_at', { withTimezone: true, mode: 'date' })

    // Relations
    reviewerId: varchar('reviewer_id', { length: 256 }),

    artifactId: varchar('dataset_id', { length: 256 }),

    creatorId: varchar('creator_id', { length: 256 }).notNull()
  },
  t => ({
    uniqueArtifact: unique('review_unique_artifact_idx').on(t.artifactId)
  })
)

export const reviewHistory = pgTable('review_history', {
  // Internal
    id: varchar('id', { length: 256 }).$defaultFn(generateId).primaryKey(),
    createdAt: timestamp('created_at', {
        withTimezone: true,
        mode: 'date'
      })
        .notNull()
        .defaultNow(),
    deletedAt: timestamp('deleted_at', { withTimezone: true, mode: 'date' })

  // Relations
  reviewId: varchar('review_id', { length: 256 })
    .notNull()
    .references(() => review.id),
  userId: varchar('user_id', { length: 256 })
    .notNull(),

  // Fields
  action: varchar('action').notNull(),
  fromState: varchar('from_state').notNull(),
  toState: varchar('to_state').notNull()
})

If I call the following function where I use a relational query with an extra field that uses sql to perform a sub-query, I get an error:

function retrieve(
  reviewId: string,
  userId: UserIdentity
): Promise<ReviewWithRelations> {
    const db = drizzle(pool, { schema })
    const foundReview = await db.query.review.findFirst({
      where: eq(review.id, reviewId),
      extras: {
        state: sql<string>`COALESCE((SELECT ${
          reviewHistory.toState
        } FROM ${reviewHistory} WHERE ${eq(
          review.id,
          reviewHistory.reviewId
        )} ORDER BY ${reviewHistory.createdAt} DESC LIMIT 1), ${initialState})`.as('state')
      }
    })

    if (!foundReview) {
      throw new NotFoundError(`Review '${reviewId}' not found`)
    }

    return foundReview
}

The error that logs in the console is error: column review.to_state does not exist.

I looked at the generated SQL for that query, and it's this:

select
"review"."id", 
"review"."created_at",
"review"."deleted_at", 
"review"."reviewer_id",
"review"."artifact_id",
"review"."creator_id",
COALESCE(
  (SELECT "review"."to_state"
          FROM "review_history"
          WHERE "review"."id" = "review"."review_id"
          ORDER BY "review"."created_at" DESC
          LIMIT 1), 
  'queued'
)
from "review"

It looks like the fields from reviewHistory don't have the correct table when rendered in the sql function, but referencing the table itself writes out the correct name.

Edit

To get it to work, I need to manually write out the field names in the sqlcall:

sql<string>`COALESCE((SELECT ${reviewHistory}."to_state" FROM ${reviewHistory} WHERE ${review.id} = ${reviewHistory}."review_id" ORDER BY ${reviewHistory},"created_at" DESC LIMIT 1), ${initialState})`.as('state')

Less type-safe, but a good enough work around for me.