Drizzle TeamDT
Drizzle Team•2y ago
Dush

Issue with SUM in sql template

db
    .select({
      total: count(),
      boPending: sql<number>`SUM(CASE WHEN ${customerFile.state} = 'BO_PENDING' THEN 1 ELSE 0 END)`,
      notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`  
    })


This query works fine if the rows are available an produce the correct result.
But for conditions that the rows are 0, boPending and notLocked result in null

I tried 
notLocked: sql<number>`SUM(CASE WHEN ${creator.level} IN ('api') THEN 1 ELSE 0 END)`.mapWith((value) => value ?? 0)

but the result is same.

Is there any issue related to this that I can follow?
Are there any alteranitives using query builer instead of sql template?
Was this page helpful?