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?

2 Replies

Mykhailo•9mo ago

hello @sq! You can try like this

  const sq = db
    .select({
      total_users: count(users.id).as('total_users'),
    })
    .from(users)
    .as('sq');

  const response = await db.select({ count: sq.total_users }).from(sq);

  const sq = db
    .select({
      total_users: count(users.id).as('total_users'),
    })
    .from(users)
    .as('sq');

  const response = await db.select({ count: sq.total_users }).from(sq);

sqOP•9mo ago

It worked, Thank you so much!

Gaming

Programming

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