DT
Drizzle Team•2y ago
vapor

Having statement to compare sql<number> and number

Sorry if a dumb question, I'm working on an aggregation query like: 
  const query = await db
    .select({
      workerUid: workerStatsDaily.workerUid,
      activeInterval: sql<number>`sum(${workerStatsDaily.activeInterval})`,
    })
    .from(workerStatsDaily)
    .where(between(workerStatsDaily.day, input.startDate, input.endDate))
    .groupBy(workerStatsDaily.workerUid)
    .having((resp) => resp.activeInterval > 3600)
}
  const query = await db
    .select({
      workerUid: workerStatsDaily.workerUid,
      activeInterval: sql<number>`sum(${workerStatsDaily.activeInterval})`,
    })
    .from(workerStatsDaily)
    .where(between(workerStatsDaily.day, input.startDate, input.endDate))
    .groupBy(workerStatsDaily.workerUid)
    .having((resp) => resp.activeInterval > 3600)
}
I'm getting a type error in the last line: Operator '>' cannot be applied to types 'SQL<number>' and 'number'. What's the best way to handle that comparison? Or maybe a having isn't the correct method? Thanks
3 Replies
vapor
vaporOP•2y ago
it seems like this is maybe a better approach?: 
  const query = db
    .select({
      workerUid: workerStatsDaily.workerUid,
      activeInterval: sql<number>`sum(${workerStatsDaily.activeInterval})`,
    })
    .from(workerStatsDaily)
    .where(between(workerStatsDaily.day, input.startDate, input.endDate))
    .groupBy(workerStatsDaily.workerUid)
    .as("query")

  const totalCount = await db
    .select({ count: sql<number>`count(*)` })
    .from(query)
    .where(gt(query.activeInterval, 3600))
  const query = db
    .select({
      workerUid: workerStatsDaily.workerUid,
      activeInterval: sql<number>`sum(${workerStatsDaily.activeInterval})`,
    })
    .from(workerStatsDaily)
    .where(between(workerStatsDaily.day, input.startDate, input.endDate))
    .groupBy(workerStatsDaily.workerUid)
    .as("query")

  const totalCount = await db
    .select({ count: sql<number>`count(*)` })
    .from(query)
    .where(gt(query.activeInterval, 3600))
Luxaritas
Luxaritas•2y ago
Yep, that’s the way to go - what you did was create a gt comparison in js (where you have the sql statement to get the number in the query, not the actual number), gt is the way to construct it in the sql query
vapor
vaporOP•2y ago
thx bud

Did you find this page helpful?