时间戳

首页 > 解决方案 > CakePHP 3.6 - 查找在两个日期之间创建的所有记录

cakephp - CakePHP 3.6 - 查找在两个日期之间创建的所有记录

问题描述

我正在寻找两个日期之间的所有记录

我的变量

 $start = '01/01/2009';
 $end = '07/24/2019';

我努力了

$gross = $this->CartOrders->find('all')->where(['placed >=' => $start])->andWhere(['placed <=' => $end])->all();

上面的查询片段

... FROM cart_orders CartOrders 
WHERE (placed >= :c0 AND placed <= :c1) 
[params] => Array ( 
     [:c0] => Array ( [value] => 01/01/2009 [type] => datetime [placeholder] => c0 ) 
     [:c1] => Array ( [value] => 07/24/2019 [type] => datetime [placeholder] => c1 ) )

结果是

 Cake\ORM\ResultSet Object ( [items] => Array ( ) )

我也试过

$gross = $this->CartOrders->find('all')->where(function($exp) use($start,$end) {
        $exp->lte('placed', $end);
        $exp->gte('placed', $start);
        return $exp;
    })->all();

我也试过

$gross = $this->CartOrders->find('all')->where(function($q) use($start,$end) {
        return $q->between('CartOrders.placed', $start, $end, 'date');
    })->all();

关于我如何做到这一点的任何想法？

标签： cakephpcakephp-3.xcakephp-3.6

解决方案

使用查询表达式

use Cake\Database\Expression\QueryExpression;

$query = $this->CartOrders->find()
->where(function (QueryExpression $exp, Query $q) use ($start,$end){
    return $exp->between('placed', $start, $end);
});

如果用户尝试在同一天内搜索，可能会在条件下添加时间

return $exp->between('placed', $start . " 00:00:00", $end . " 23:59:59");

推荐阅读