Yii 2 如何获取当天的所有数据

Question

基本上是产品报价表。 迁移

$this->createTable('product_offer', [
'id'            => $this->primaryKey(),
'product_id'    => $this->bigInteger(20) . ' NOT NULL',
'customer_id'   => $this->bigInteger(20),
'coupon_code'   => $this->string(),
'offer'         => $this->string(),
'customer_name' => $this->string(),
'email_address' => $this->string(),
'phone_number'  => $this->string(),
'note'          => $this->text(),
'admin_note'    => $this->text(),
'status'        => $this->tinyInteger(),
'created'       => $this->dateTime() . ' NOT NULL',
'modified'      => $this->dateTime(),
]);

现在我在此表中有数据并创建了查询。

$stamp = mktime(0, 0, 0);
$testQuery = self::find()->where([
'and',
['=', 'status', self::STATUS_REJECTED],
['=', 'product_id', $this->product_id],
['=', 'email_address', $this->email_address],
['>', 'created', date('m-d-Y H:i:s',$stamp)]
]);
echo "<pre>";
print_r($testQuery->all());
echo "</pre>";

结果是

Array
(
[0] => appmodelsProductOffer Object
(
[verifyCode] => 
[_attributes:yiidbBaseActiveRecord:private] => Array
(
[id] => 48
[product_id] => 111
[customer_id] => 
[coupon_code] => 
[offer] => 23
[customer_name] => Aljay Mallari
[email_address] => [e-mail]
[phone_number] => 958568574
[note] => Test
[admin_note] => 
[status] =>  Rejected 
[created] => 2018-05-16 04:38:28
[modified] => 
)
[_oldAttributes:yiidbBaseActiveRecord:private] => Array
(
[id] => 48
[product_id] => 111
[customer_id] => 
[coupon_code] => 
[offer] => 23
[customer_name] => Aljay Mallari
[email_address] => [e-mail]
[phone_number] => 958568574
[note] => Test
[admin_note] => 
[status] => 0
[created] => 2018-05-16 04:38:28
[modified] => 
)
[_related:yiidbBaseActiveRecord:private] => Array
(
)
[_relationsDependencies:yiidbBaseActiveRecord:private] => Array
(
)
[_errors:yiibaseModel:private] => 
[_validators:yiibaseModel:private] => 
[_scenario:yiibaseModel:private] => default
[_events:yiibaseComponent:private] => Array
(
)
[_eventWildcards:yiibaseComponent:private] => Array
(
)
[_behaviors:yiibaseComponent:private] => Array
(
)
)
[1] => appmodelsProductOffer Object
(
[verifyCode] => 
[_attributes:yiidbBaseActiveRecord:private] => Array
(
[id] => 50
[product_id] => 111
[customer_id] => 
[coupon_code] => 
[offer] => 23
[customer_name] => Aljay Mallari
[email_address] => [e-mail]
[phone_number] => 958568574
[note] => Test
[admin_note] => 
[status] =>  Rejected 
[created] => 2018-05-17 04:39:55
[modified] => 
)
[_oldAttributes:yiidbBaseActiveRecord:private] => Array
(
[id] => 50
[product_id] => 111
[customer_id] => 
[coupon_code] => 
[offer] => 23
[customer_name] => Aljay Mallari
[email_address] => [e-mail]
[phone_number] => 958568574
[note] => Test
[admin_note] => 
[status] => 0
[created] => 2018-05-17 04:39:55
[modified] => 
)
[_related:yiidbBaseActiveRecord:private] => Array
(
)
[_relationsDependencies:yiidbBaseActiveRecord:private] => Array
(
)
[_errors:yiibaseModel:private] => 
[_validators:yiibaseModel:private] => 
[_scenario:yiibaseModel:private] => default
[_events:yiibaseComponent:private] => Array
(
)
[_eventWildcards:yiibaseComponent:private] => Array
(
)
[_behaviors:yiibaseComponent:private] => Array
(
)
)
[2] => appmodelsProductOffer Object
(
[verifyCode] => 
[_attributes:yiidbBaseActiveRecord:private] => Array
(
[id] => 51
[product_id] => 111
[customer_id] => 
[coupon_code] => 
[offer] => 23
[customer_name] => Aljay Mallari
[email_address] => [e-mail]
[phone_number] => 958568574
[note] => test
[admin_note] => 
[status] =>  Rejected 
[created] => 2018-05-17 04:43:29
[modified] => 
)
[_oldAttributes:yiidbBaseActiveRecord:private] => Array
(
[id] => 51
[product_id] => 111
[customer_id] => 
[coupon_code] => 
[offer] => 23
[customer_name] => Aljay Mallari
[email_address] => [e-mail]
[phone_number] => 958568574
[note] => test
[admin_note] => 
[status] => 0
[created] => 2018-05-17 04:43:29
[modified] => 
)
[_related:yiidbBaseActiveRecord:private] => Array
(
)
[_relationsDependencies:yiidbBaseActiveRecord:private] => Array
(
)
[_errors:yiibaseModel:private] => 
[_validators:yiibaseModel:private] => 
[_scenario:yiibaseModel:private] => default
[_events:yiibaseComponent:private] => Array
(
)
[_eventWildcards:yiibaseComponent:private] => Array
(
)
[_behaviors:yiibaseComponent:private] => Array
(
)
)
[3] => appmodelsProductOffer Object
(
[verifyCode] => 
[_attributes:yiidbBaseActiveRecord:private] => Array
(
[id] => 55
[product_id] => 111
[customer_id] => 
[coupon_code] => 
[offer] => 23
[customer_name] => Aljay Mallari
[email_address] => [e-mail]
[phone_number] => 958568574
[note] => Test
[admin_note] => 
[status] =>  Rejected 
[created] => 2018-05-17 04:51:57
[modified] => 
)
[_oldAttributes:yiidbBaseActiveRecord:private] => Array
(
[id] => 55
[product_id] => 111
[customer_id] => 
[coupon_code] => 
[offer] => 23
[customer_name] => Aljay Mallari
[email_address] => [e-mail]
[phone_number] => 958568574
[note] => Test
[admin_note] => 
[status] => 0
[created] => 2018-05-17 04:51:57
[modified] => 
)
[_related:yiidbBaseActiveRecord:private] => Array
(
)
[_relationsDependencies:yiidbBaseActiveRecord:private] => Array
(
)
[_errors:yiibaseModel:private] => 
[_validators:yiibaseModel:private] => 
[_scenario:yiibaseModel:private] => default
[_events:yiibaseComponent:private] => Array
(
)
[_eventWildcards:yiibaseComponent:private] => Array
(
)
[_behaviors:yiibaseComponent:private] => Array
(
)
)
);

今天在我们的时区是 5 年 17 月 2018 日，我想获取今天创建的数据，但似乎我的查询并不排除昨天创建的数据。

我已经尝试表达这个

['<', 'created', new Expression('NOW()')]

而这个

$testQuery = self::find()->where([
'and',
['=', 'status', self::STATUS_REJECTED],
['=', 'product_id', $this->product_id],
['=', 'email_address', $this->email_address],
['>', 'created', new Expression(date('m-d-Y H:i:s', $stamp))]
]);
echo "<pre>";
print_r($testQuery->all());
echo "</pre>";

现在我收到一个SQL错误

数据库异常 – yii\db\异常 SQLSTATE[42000]：语法错误或 访问冲突：1064 SQL 语法有错误;检查 与您的 MySQL 服务器版本相对应的正确手册 在第 1 行的"00：00：00"附近使用的语法 正在执行的 SQL 是： 从product_offer中选择 *，其中 (status= 0) 和 (product_id= "111") 和 (email_address= "[e-mail]") 和 (created>05-17-2018 00：00：00)

Answer 1

您可以将原始 SQL 内容与字符串格式的WHERE调用一起使用，也可以将两个WHERE调用一起使用。 综上所述，我们可以尝试以下方法：

$testQuery = self::find()->where([
'and',
['=', 'status', self::STATUS_REJECTED],
['=', 'product_id', $this->product_id],
['=', 'email_address', $this->email_address]
])
->andWhere('created >= CURDATE()');

请注意，如果您想限制为今天任何时候创建的记录，我认为您要在此处使用对应于今天午夜的CURDATE。

Answer 2

您使用了不正确的日期格式。应该date('Y-m-d H:i:s', $stamp)通过邮票或date('Y-m-d 00:00:00', $stamp)强制午夜。

$testQuery = self::find()->where([
'and',
['=', 'status', self::STATUS_REJECTED],
['=', 'product_id', $this->product_id],
['=', 'email_address', $this->email_address],
['>', 'created', date('Y-m-d 00:00:00', $stamp)]
]);

---

此外，您以错误的方式使用yiidbExpression。你不能直接将值传递给Expression，这可能会让你进入SQL注入。如果需要，您应该使用参数。

$testQuery = self::find()->where([
'and',
['=', 'status', self::STATUS_REJECTED],
['=', 'product_id', $this->product_id],
['=', 'email_address', $this->email_address],
new Expression('created > :date', [':date' => date('Y-m-d 00:00:00', $stamp)]),
]);