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
Jump to solution
5 Replies
html_extraordinaire
FYI this works if I execute the issuances query first and just use the value from the row that gets returned.
Solution
rphlmr ⚡
rphlmr ⚡4mo ago
👋 you can use a plain query as sub select: https://drizzle.run/yxbxn6x4rloxney2nvc1tphp
html_extraordinaire
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 ⚡
rphlmr ⚡4mo 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
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?
Want results from more Discord servers?
Add your server