Drizzle TeamDT
Drizzle Team•2y ago
sq

How to select specific fields in subquery and reference it in parent query?

I have a subquery that selects the count of a field, like
const sq = this.db.ormService!.drizzle
  .select(
    {
      total_users: count(user.user_id),
    }
  )
  .from(user)
  .where(isNotNull(user.username))
  .as('sq');

const q = this.db.ormService!.drizzle
  .select()
  .from(sq);

However, when I call q.execute() it errors out with the message You tried to reference \"total_users\" field from a subquery, which is a raw SQL field, but it doesn't have an alias declared. Please add an alias to the field using \".as('alias')\" method.

I am not sure what I'm doing wrong as the subquery is aliased using the .as method.
Any ideas on what I should change to fix this?
Was this page helpful?