Subquery in select, and referencing value from outer query

Hello! I am trying to convert this postgres query from SQL til drizzle syntax.

SELECT
  SMU."year",
  SMU."month",
  (
    SELECT COALESCE(SUM(UCALL."duration"), 0) AS CALLS_SECONDS
    FROM SUBSCRIPTION_USAGE_CALLS_STARTED AS UCALL
    WHERE UCALL.SUBSCRIPTION_MONTHLY_USAGE_ID = SMU.SUBSCRIPTION_MONTHLY_USAGE_ID
  ) 
FROM SUBSCRIPTION_MONTHLY_USAGE AS SMU;

SELECT
  SMU."year",
  SMU."month",
  (
    SELECT COALESCE(SUM(UCALL."duration"), 0) AS CALLS_SECONDS
    FROM SUBSCRIPTION_USAGE_CALLS_STARTED AS UCALL
    WHERE UCALL.SUBSCRIPTION_MONTHLY_USAGE_ID = SMU.SUBSCRIPTION_MONTHLY_USAGE_ID
  ) 
FROM SUBSCRIPTION_MONTHLY_USAGE AS SMU;

How do i use subqueries in select in drizzle? Is it correct to left join the subquery, matching on a FK and then selecting the property in the select? As described in this article: https://www.davegray.codes/posts/how-to-write-a-sql-subquery-with-drizzle-orm // Note: The reason for using subquery in this case is because i am dealing with a large dataset and performance testing shows that joining is substantially slower than subqueries.

Dave Gray's Blog

How to Write a SQL Subquery with Drizzle ORM

SQL subqueries in Drizzle ORM.

3 Replies

CMarker•2mo ago

I've tried this: but i'm getting the error error: invalid reference to FROM-clause entry for table "subscription_monthly_usage"

const callsSecondsSubQuery = this.db
    .select({
        id: subscriptionUsageCallsStarted.subscriptionMonthlyUsageId,
        callSeconds: sum(subscriptionUsageCallsStarted.duration).as('callSeconds'),
    })
    .from(subscriptionUsageCallsStarted)
    .where(eq(subscriptionUsageCallsStarted.subscriptionMonthlyUsageId, this.table.subscriptionMonthlyUsageId))
    .as('callSeconds');

const result = await this.db
    .select({
        year: this.table.year,
        month: this.table.month,
        subscriptionMonthlyUsageId: this.table.subscriptionMonthlyUsageId,
        callSeconds: callsSecondsSubQuery.callSeconds,
    })
    .from(this.table)
    .leftJoin(callsSecondsSubQuery, eq(callsSecondsSubQuery.id, this.table.subscriptionMonthlyUsageId))

const callsSecondsSubQuery = this.db
    .select({
        id: subscriptionUsageCallsStarted.subscriptionMonthlyUsageId,
        callSeconds: sum(subscriptionUsageCallsStarted.duration).as('callSeconds'),
    })
    .from(subscriptionUsageCallsStarted)
    .where(eq(subscriptionUsageCallsStarted.subscriptionMonthlyUsageId, this.table.subscriptionMonthlyUsageId))
    .as('callSeconds');

const result = await this.db
    .select({
        year: this.table.year,
        month: this.table.month,
        subscriptionMonthlyUsageId: this.table.subscriptionMonthlyUsageId,
        callSeconds: callsSecondsSubQuery.callSeconds,
    })
    .from(this.table)
    .leftJoin(callsSecondsSubQuery, eq(callsSecondsSubQuery.id, this.table.subscriptionMonthlyUsageId))

Angelelz•2mo ago

the first issue I see is that you have an aggregage function in you subquery (sum) without a group by clause. I'm guessing you want to group by id I don't see anything else that's obvious

CMarker•2mo ago

Hi. Yes i found out yesterday that that was the case.

Gaming

Programming

Subquery in select, and referencing value from outer query