mysql - CakePHP 3 : How to count and retrieve data for different periods in one query?

Question

我想通过创建日期字段统计和检索不同时期的数据，例如今天、总计、本周等。

如何计算例如 this_week？

这里是起始代码:

public function findTotalRegistered(Query $query, Array $options)
  {
    $total = $query->func()->count('id');

    $query
      ->select([
        'total' => $total,
        //'today' => $today,
        //'this_week' => $this_week,
        //'this_month' => $this_month,
        //'this_year' => $this_year
      ]);

    return $query;
  }

Answer 1

您可以通过执行子查询来实现此目的:

public function findTotalRegistered(Query $query, Array $options)
{
    $total = $query->func()->count('id');

    $query
        ->select([
            'total' => $total,
            'today' => $query->where('DATE(created) = CURDATE()')->count(),
            'this_week' => $query->where('YEARWEEK(DATE(created), 1) = YEARWEEK(CURDATE(), 1))')->count(),
            'this_month' => $query->where('DATE_SUB(NOW(), INTERVAL 1 MONTH)')->count(),
            'this_year' => $query->where('YEAR(created) = YEAR(CURDATE())')->count()
        ])
        ->group(created);

    return $query;
}

谢谢