CakePHP Model with "Between dates"

Question

I have a large data set (over a billion rows). The data is partitioned in the database by date. As such, my query tool MUST specify an SQL between clause on every query, or it will have to scan every partition.. and well, it'll timeout before it ever comes back.

So.. my question is, the field in the database thats 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 bring me two a second question.. how do I validate that in a model which is linked to a table?

Answer 1

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

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

As far as validating, you could use the model's beforeValidate() callback to validate the two dates. More info on this 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 that answer your question?

Answer 2

If I am following you correctly:

• The user must specify start/end dates for find queries generated from a form
• You need to validate these dates so that, for example:
  • end date after start date
  • end date not centuries away from start date
• You want validation errors appearing inline within the form (even though this isn't a save)

Since you want to validate these dates they will be harder to grab when they are tucked away inside your conditions array. I suggest trying to pass these in separately and then dealing with them later:

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

You should hopefully be able to 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 find operation, so this might not even work. The idea is that if the form is created using FormHelper these messages should make it back next to the form fields.

Failing that, you might need to perform this validation in the controller and use Session::setFlash(). if so, you can also get rid of the beforeFind and put the BETWEEN condition array in with your other conditions.