Drizzle Team•2mo ago

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

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,
  })

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

5 Replies

html_extraordinaire•2mo ago

FYI this works if I execute the issuances query first and just use the value from the row that gets returned.

Solution

rphlmr ⚡•2mo ago

👋 you can use a plain query as sub select: https://drizzle.run/yxbxn6x4rloxney2nvc1tphp

Drizzle Run

Select with sub query - Drizzle Run

html_extraordinaire•2mo ago

That worked! Thank you! Ah, question though. Does this query happen on each row that gets returned or just once and the result is "cached"?

rphlmr ⚡•2mo ago

Good question. The db engine is capable of optimizing sub queries in from and cte (queries with “with”). In this playground I don’t think it is cached and it happens to every row since it is called in a select.

html_extraordinaire•2mo ago

Appreciate the help, thank you! So what I ended up doing was doing a .leftJoin(subquery, sql'true') instead of putting it into select but would it be even better to put it in a CTE? What would the difference be if any?

Gaming

Programming

Subquery in select statement?