2 replies

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

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.

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

.as

method.
Any ideas on what I should change to fix this?

Drizzle Team•2y ago•

2 replies

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

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.

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

.as

method.
Any ideas on what I should change to fix this?

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

Similar Threads

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

Similar Threads

Similar Threads

Similar Threads