node.js - mysql如何通过knex查询今天和七天内的汇总数据

Question

具体实现是要在product表中查询出今天、七天和三十天内的产品数量，具体的sql语句已经写好了

select sum(inputer as productNum) from `product` where to_days(`createdAt`)= to_days(now());

但是在knex.js里面我这样写根本不对

  return knex('product')
  .where({ inputer: user, deletedAt: null })     
  .andWhere('to_days(add_time)', '=', 'to_days(now())')
  .sum('inputer as productNum')
  .then(function (productRow) {
    return { product: productRow };
  })

用having也不对,knex文档里没有看到聚合函数的使用方法，求指教

  return knex('product')
  .where({ inputer: user, deletedAt: null })     
  .groupBy(id)
  .having('to_days(add_time)', '=', 'to_days(now())')
  .sum('inputer as productNum')
  .then(function (productRow) {
    return { product: productRow };
  })

Answer 1

I have never used knex.js, but the SQL seems to be complicated (the original SQL will perform operations on the createdAt field, which may invalidate the index of this field).

SELECT sum(inputer) AS product_num FROM `product`
WHERE createdAt >= ?

Calculate the starting time of today, seven days ago and thirty days ago (i.e. yyyy-MM-dd 00:00:00) through the program, and then substitute it into SQL.