Drizzle TeamDT
Drizzle Team•2y ago
html_extraordinaire

Subquery in select statement?

Hi, I'm trying to grab a value from a subquery in my select statement for a computed column but I'm getting an error telling me the column from the subquery doesn't exist.
const issuancesSq = tx
  .select({
    totalAcreFeetPerShare: sum(issuance.acreFeetPerShare)
      .mapWith(Number)
      .as('totalAcreFeetPerShare'),
  })
  .from(issuance)
  .innerJoin(
    irrigationYear,
    eq(issuance.irrigationYearId, irrigationYear.id)
  )
  .where(eq(irrigationYear.id, input.irrigationYearId))
  .as('issuancesSq');

Attempting to use it inside of the select, here:
const irrigationClients = await tx
  .select({
    available:
      sql`((${irrigationYearShares.shares} * ${issuancesSq.totalAcreFeetPerShare}::numeric) + COALESCE(${auxiliaryIssuancesSq.available}, 0)) - COALESCE(${shrinkSq.shrink}, 0)`.mapWith(
        Number
      ),
    firstName: user.firstName,
    irrigationClientId: irrigationClient.id,
    lastName: user.lastName,
    shares: irrigationYearShares.shares,
    unit: irrigationsSq.unit,
    used: irrigationsSq.used,
  })
Solution
👋 you can use a plain query as sub select: https://drizzle.run/yxbxn6x4rloxney2nvc1tphp
Drizzle Run
Select with sub query - Drizzle Run
Jump to solution
Was this page helpful?