CMarker Posts - Answer Overflow

CMarker

•Created by CMarker on 9/9/2024 in #help

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.

5 replies

Gaming

Programming