具有“两个日期之间”的CakePHP模型 [英] CakePHP Model with "Between dates"

问题描述

我有一个大型数据集（超过十亿行）。数据按日期在数据库中进行分区。因此，我的查询工具必须在每个查询中指定一个SQL between子句，否则它将必须扫描每个分区..好吧，它会在它返回之前超时。

$ b $我的问题是，数据库中的分区的字段是一个日期..

使用CakePHP，如何指定之间日期我的形式？

我想在表单本身做start_date和end_date，但这可能会带来两个问题...如何验证

解决方案

如果我正确关注你：

• 用户必须为从表单生成的查询查询指定开始/结束日期


• 您需要验证这些日期，例如：
  
  
  
  • 开始日期后结束日期
  
  
  • 结束日期距离开始日期不是几个世纪
  
  


• 您希望验证错误在表单中内嵌显示（即使这不是保存）

因为你想验证这些日期，当他们被封装在你的条件数组中时，他们将很难抓住。我建议分开传递这些，然后再处理：

  $ this-> Model-> find 'all'，array（
'conditions'=> array（/ * normal conditions here * /），
'dateRange'=> array（
'start'=> * start_date value * /，
'end'=> / * end_date value * /，
），
） 
  

你应该希望能够处理 beforeFind filter：

  public function beforeFind（）{
 //执行查询验证
 if （
 $ this-> invalidate（
 / * end_date字段名称*）（$ queryData ['dateRange'] ['end']< $ queryData ['dateRange'] ['start']） /，
结束日期必须晚于开始日期
）; 
 return false; 
} 
 / *重复其他验证* / 
 //在查询之间添加条件
 $ queryData ['condition'] [] = array（
'Model .dateField BETWEEN？AND？'=> array（
 $ queryData ['dateRange'] ['start']，
 $ queryData ['dateRange'] ['end']，
 ），
）; 
 unset（$ queryData ['dateRange']）; 
 //继续查找
 return true; 
} 
  

我没有尝试使用 Model :: invalidate ），所以这可能不工作。想法是，如果使用 FormHelper 创建表单，这些邮件应该返回到表单字段旁边。

如果没有，您可能需要在控制器中执行此验证并使用 Session :: setFlash（）。如果是这样，你也可以摆脱 beforeFind 并把 BETWEEN 条件数组与你的其他条件。 / p>

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?

解决方案

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.

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