我需要在Cake 3中计算今天创建的帐户 [英] I need to count the today created account in Cake 3

问题描述

我需要对用户进行计数，但我的条件是仅在今天已创建他们的帐户的情况下。我有一个用户表，每行都有一个创建的字段（日期时间）。我如何在Cakephp中做到这一点，我在文档中找不到答案。

I need to count the users, but my condition is only if their account have been created today. I have a users table with a created field (datetime) for each rows. How can i do it in Cakephp, i didn't find the answer in the documentation.

$usersNewCount = Number::format($this->Users->find()->where(['created' => 'CURDATE()'])->count());

我尝试过CURDATE，当然它不起作用，我猜Cakephp具有特定的功能datetime字段？

I tried with CURDATE, and of course it's not working, i guess Cakephp has a specific function for te datetime field ?

推荐答案

您在此处所做的操作由于各种原因将无法正常工作。

What you are doing there won't work for various reasons.

1. 您不能在条件数组的value部分中传递SQL代码段，它将被转义，并且您将得到类似于 created ='CURDATE（）'，您要么必须将整个条件作为字符串传递，要么使用 原始表达式 。

1. You cannot pass SQL snippets in the value part of the conditions array, it will be escaped and you'll end up with a string comparison like created = 'CURDATE()', you'd either have to pass the whole condition as a string, or use raw expressions.

即使正确地传递了 CURDATE（），由于创建的 列具有时间部分，因此该比较也不起作用。

Even when properly passing CURDATE(), the comparison won't work as the created column has a time part.

虽然可以通过转换列来规避前一个问题，但您应尽可能避免这种情况！与 DATE（created）= CURDATE（）之类的计算列进行比较将使使用索引成为不可能，从而大大降低了性能！

While it is possible to circumvent the former problem by transforming the column, you should try to avoid that whenever possible! Comparing to calculated columns like DATE(created) = CURDATE() will make using indices impossible, and thus massively degrade performance!

因此，除非您有多余的列仅包含日期部分，否则最好的选择是 BETWEEN 比较，它等效于 a> ; = x AND a< = y ，并且为了保持跨DBMS的兼容性，最好通过从PHP传递日期来完成此操作，即不使用DBMS特定的日期和时间函数，例如 CURDATE（）。

So unless you have an extra column that holds just the date part, your best bet is a BETWEEN comparison which is the equivalent to a >= x AND a <= y, and in order to stay cross DBMS compatible, this is best to be done by passing dates from PHP, ie not using DBMS specific date and time functions like CURDATE().

$this->Users
    ->find()
    ->where(function (\Cake\Database\Expression\QueryExpression $exp, \Cake\ORM\Query $query) {
        $from = (new \DateTime())->setTime(0, 0, 0);
        $to = (new \DateTime())->setTime(23, 59, 59);
        return $exp->between('Users.created', $from, $to, 'datetime');
    })
    ->count()

这将创建类似于

SELECT
    (COUNT(*)) AS `count`
FROM
    users Users
WHERE
    Users.created BETWEEN '2015-05-26 00:00:00' AND '2015-05-26 23:59:59'

另请参见

• API> \Cake\Database\Expression\QueryExpression :: between（）

这篇关于我需要在Cake 3中计算今天创建的帐户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！