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

ua4mk5z4 于 2022-11-11 发布在 PHP

关注(0)|答案(4)|浏览(109)

我正在查找两个日期之间的所有记录
我的变量

$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();

我该怎么做呢？

cakephp

来源：https://stackoverflow.com/questions/57173609/cakephp-3-6-find-all-records-created-between-two-dates

4条答案

按热度按时间

enyaitl31#

使用查询表达式

use Cake\Database\Expression\QueryExpression;

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

如果用户试图在同一天内搜索，则可能会添加一个time at条件

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

赞(0）回复(0）举报 2022-11-11

hc8w905p2#

请尝试使用

$this->CartOrders->find('all', array('conditions' => array(
        'date(placed) BETWEEN "'.$start.'" AND "'.$end.'"')));

这是一个非正统的解决方案，但它已经为我的多种情况工作

赞(0）回复(0）举报 2022-11-11

elcex8rz3#

$this->set('gross',$this->CartOrders-> find(
                'all', array(
                    'conditions' => array(
                        'CartOrders.placed >=' => $start,
                        'CartOrders.placed <=' => $end
                    ))
            ));  // here gross is a variable to store the data from DB and CartOders is the Model name

赞(0）回复(0）举报 2022-11-11

olhwl3o24#

这是一个日期格式问题。
以下解决了我的问题。

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

$start = DateTime::createFromFormat('d/m/Y', $start);
$end = DateTime::createFromFormat('d/m/Y', $end);

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

此链接帮助
PHP convert date format dd/mm/yyyy => yyyy-mm-dd

赞(0）回复(0）举报 2022-11-11

我来回答

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

4条答案

相关问题

热门标签

最新问答