CakePHP Between Dates Model

Question

I have a large data set (more than a billion rows). Data is shared in the database by date. Thus, my query tool MUST indicate SQL between the statement in each query, or it will have to scan each section .. and, well, it will time out before it ever returns.

So my question is: the field in the database that is partitioned is a date.

Using CakePHP, how can I specify “between” dates in my form?

I was thinking about doing "start_date" and "end_date" in the form itself, but this may cause me a second question .. how can I test this in the model associated with the table?

Answer 1

If I follow you correctly:

• The user must specify the start and end dates of the search for queries generated from the form
• You need to confirm these dates, for example:
  • end date after start date
  • end date not far from start date.
• You want validation errors to appear inside the form (although this is not a save)

Since you want to check these dates, it will be harder to capture them when they are hidden inside your conditions array. I suggest trying to transfer them separately, and then talk to them later:

$this->Model->find('all', array( 'conditions' => array(/* normal conditions here */), 'dateRange' => array( 'start' => /* start_date value */, 'end' => /* end_date value */, ), )); 

You can probably handle everything else in the beforeFind filter:

 public function beforeFind() { // perform query validation if ($queryData['dateRange']['end'] < $queryData['dateRange']['start']) { $this->invalidate( /* end_date field name */, "End date must be after start date" ); return false; } /* repeat for other validation */ // add between condition to query $queryData['conditions'][] = array( 'Model.dateField BETWEEN ? AND ?' => array( $queryData['dateRange']['start'], $queryData['dateRange']['end'], ), ); unset($queryData['dateRange']); // proceed with find return true; } 

I have not tried using Model::invalidate() during a search operation, so this may not even work. The idea is that if a form is created using FormHelper , these messages should return to the form fields.

Otherwise, you may need to perform this check in the controller and use Session::setFlash() . if so, you can also get rid of beforeFind and put BETWEEN in the array of conditions with other conditions.

Answer 2

if you want to find the latest data for 20 days.

  $this->loadModel('User'); //$this->User->recursive=-1; $data=$this->User->find('all', array('recursive' => 0, 'fields' => array('Profile.last_name','Profile.first_name'),'limit' => 20,'order' => array('User.created DESC'))); 

another wise between two dates

 $start = date('Ym-d') ; $end = date('Ym-d', strtotime('-20 day')); $conditions = array('User.created' =>array('Between',$start,$end)); $this->User->find("all",$conditions) 

Answer 3

You can write a custom method in your model to search between dates:

 function findByDateRange($start,$end){ return $this->find('all',array('date >= '.$start,'data >= .'$end)); } 

As for validation, you can use the beforeValidate() model beforeValidate() to validate two dates. Read more about it here .

 function beforeValidate(){ if(Validation::date($this->data['Model']['start_date'])){ return false; } if(Validation::date($this->data['Model']['end_date'])){ return false; } return parent::beforeValidate(); } 

Does this answer your question?