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

vaporOP•16mo 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•16mo 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

vaporOP•16mo ago

thx bud

Gaming

Programming

Having statement to compare sql<number> and number