CakePHP finds a condition for a query between two dates

Question

I have a start and end date in my database and a date date variable from a form field. Now I'm trying to query all the lines where $ date is = start / end of the date in db, or ANY date between the two.

This is the opposite of what is described in the docs on how daysAsSql works. I can’t figure out how to make it work. The following line does not work as a search condition in the controller:

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

Any help is appreciated. It drives me crazy.

Here is the complete query and the 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)) 

Answer 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 code above and ask if it works for you.

Edit: At @Aryan's request, if we need to find users registered within a 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)'))); 

Answer 2

Here is an example CakePHP BETWEEN request.

I define my arrays as variables, and then using these variables in my CakePHP search function call:

 // just return these two fields $fields = array('uri', 'page_views'); // use this "between" range $conditions = array('Event.date BETWEEN ? and ?' => array($start_date, $end_date)); // run the "select between" query $results = $this->Event->find('all', array('fields'=>$fields, 'conditions'=>$conditions)); 

Link from

Answer 3

Common sample request for CakePHP 2.x

  $_condition = array("TABLENAME.id" => $id, "TABLENAME.user_id" => array_unique($_array), 'date(TABLENAME.created_at) BETWEEN ? AND ?' => array($start_date, $end_date)); $result_array = $this->TABLENAME->find("all", array( 'fields' => array("TABLENAME.id", "TABLENAME.user_id", 'TABLENAME.created_at'), "conditions" => $_condition, "group" => array("TABLENAME.id"), //fields to GROUP BY 'joins' => array( array( 'alias' => 'T2', 'table' => 'TABLENAME2', 'type' => 'LEFT', 'conditions' => array('TABLENAME.t_id = TABLENAME2.t_id') ), array( 'alias' => 'T3', 'table' => 'TABLENAME3', 'type' => 'LEFT', 'conditions' => array( 'IF( TABLENAME.t3_id > 0, T2.f_id = T3.f_id, TABLENAME.ff_id = T2.ff_id )' ) ), ), 'recursive' => 0 ) ); 

Answer 4

This is a more efficient and clear IN BETWEEN request in cakephp 2.x

 $testing_log_device_site_name = $testingLogData['TestingLogDevice']['Siteid']; $conditions = array('TestingLogDevice.dateee BETWEEN ? and ?' => array($start_date, $end_date)); $results = $this->TestingLogDevice->find('all', array( 'fields'=>array('dateee','timeee','Siteid'), 'conditions'=>array($conditions, 'TestingLogDevice.Siteid'=>$testing_log_device_site_name) ) ); pr($results);