sq Posts - Answer Overflow

Explore posts from servers

•Created by sq on 4/19/2024 in #help

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);

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?

3 replies

Gaming

Programming