CakePHP查找两个日期之间的查询的条件 [英] CakePHP find condition for a query between two dates

问题描述

我有一个开始和结束日期在我的数据库和$ date变量从表单字段。我现在试图查询所有行，其中$ date是db中的开始/结束日期，或两者之间的任何日期。

I have a start and an end date in my database and a $date variable from a form field. I am now trying to query all the rows where $date is either = start/end date in the db, or ANY date between those two.

这是相反的在asAsql如何工作的文档中描述的内容。我不知道如何让它工作。以下行不能作为控制器中的查找条件：

It's kind of the opposite of what is described in the docs of how daysAsSql works. I can't figure out how to get it to work. The following line does not work as a find condition in the controller:

'? BETWEEN ? AND ?' => array($date, 'Item.date_start', 'Item.date_end'),

不胜感激。

这里是完整的查询和对应的SQL：

Here is the complete Query and corresponding SQL:

$conditions = array(
            'conditions' => array(
            'and' => array(
                '? BETWEEN ? AND ?' => array($date, 'Item.date_start', 'Item.date_end'),
                'Item.title LIKE' => "%$title%",
                'Item.status_id =' => '1'
                )));

        $this->set('items', $this->Item->find('all', $conditions));



WHERE (('2012-10-06' BETWEEN 'Item.date_start' AND 'Item.date_end') AND (`Item`.`title` LIKE '%%') AND (`Item`.`status_id` = 1))

推荐答案

$conditions = array(
        'conditions' => array(
        'and' => array(
                        array('Item.date_start <= ' => $date,
                              'Item.date_end >= ' => $date
                             ),
            'Item.title LIKE' => "%$title%",
            'Item.status_id =' => '1'
            )));

尝试上述代码，询问是否适用于您。

Try the above code and ask if it not worked for you.

编辑：
根据@Aryan请求，如果我们要查找在1个月之间注册的用户：

As per @Aryan request, if we have to find users registered between 1 month:

$start_date = '2013-05-26'; //should be in YYYY-MM-DD format
$this->User->find('all', array('conditions' => array('User.reg_date BETWEEN '.$start_date.' AND DATE_ADD('.$start_date.', INTERVAL 30 DAY)')));

这篇关于CakePHP查找两个日期之间的查询的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！