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

问题描述

我有一个大的数据集（十亿行）。数据按日期分区。因此，我的查询工具必须在每个查询上指定一个SQL between子句，否则它将不得不扫描每个分区。而且，它将在它回来之前超时。

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

使用CakePHP，我如何在我的表单？

我正在考虑在表单本身做start_date和end_date，但这可能会给我两个第二个问题..我如何验证在与表相关的模型中？

解决方案

如果我正在关注您的问题：

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


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


• 您希望在fo内出现验证错误rm（即使这不是保存）

由于您要验证这些日期，因此他们将难以抓住藏在你的条件阵列内。我建议尝试单独传递这些，然后再处理它们：

  $ 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 （$ queryData ['dateRange'] ['end']< $ queryData ['dateRange'] ['start']）{
 $ this-> invalidate（
 / * end_date字段名* /，
结束日期必须在开始日期后
）; 
返回false; 
} 
 / *重复其他验证* / 
 //在查询条件之间添加
 $ queryData ['conditions'] [] = 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屋！