按函数 CakePHP 对具有计算字段的高级搜索进行分页、过滤和排序

Question

我正在寻找更好的解决方案来解决此类问题。我需要对具有许多搜索选项的公寓进行高级搜索：关键字、类别、开始日期、日期到、价格范围（最低价格、最高价格）和其他字段......如果您选择两个日期，我会计算每间公寓的租金价格。要计算价格，我不能使用 virtualField 或一个或多个子查询，但我必须使用模型 Apartment 的函数，因为它执行许多计算......

所以我决定使用 afterFind 的回调来计算租金价格，并在查询结果数组中添加一个"列"rental_price：

public function afterFind($results, $primary){
   if($this->dateFrom && $this->dateTo){
      foreach ($results as $key => $val) {
         if(isset($val['Apartment']['id'])){
            $results[$key]['Apartment']['rental_price'] = $this->calculateRentalPrice($val['Apartment']['id'], $this->dateFrom, $this->dateTo);
         }
      }
   }
   return $results;
}

在控制器上：

$this->paginate = array( ... ); // Array with the query
$apartments = $this->paginate('Apartment');
// EDIT
foreach ($apartments as $key => &$apartment) {
   if($apartment['Apartment']['rental_price'] < $priceMin || $apartment['Apartment']['rental_price'] > $priceMax){
      unset($apartments[$key]);
   }
}
$this->set('apartments', $apartments);

问题是现在我想用最低价格和最高价格重新过滤数组$apartments，但是如果我直接操作$apartments我就会遇到分页问题。解决这种情况的最佳方法是什么？

   // EDIT
   public function calculateRentalPrice($apartmentId, $dateFrom, $dateTo){
      $from = new DateTime($dateFrom);
      $to = new DateTime($dateTo);
      $period = new DatePeriod($from, DateInterval::createFromDateString('1 day'), $to);
      $rentalPrice = 0;
      foreach ($period as $day){
         $date = $day->format("Y-m-d");
         $dailyPrice = $this->ApartmentSeason->getDailyPrice($apartmentId, $date);
         $rentalPrice += $dailyPrice; 
      }
      return $rentalPrice;
   }
   public function getDailyPrice($apartmentId, $date){
      $apartmentSeason = $this->find('first', array(
         'conditions' => array(
            'ApartmentSeason.apartment_id' => $apartmentId,
            "'{$date}' BETWEEN ApartmentSeason.date_from AND ApartmentSeason.date_to", 
            "ApartmentSeason.date_to != " => $date, 
         )
      ));
      $dailyPrice = $apartmentSeason['ApartmentSeason']['price'] / 7;
      return $dailyPrice;
   }

Answer 1

解决此问题的一种方法是为日期范围内的每个价格绑定一个模型，然后在虚拟字段中将它们相加。可能有一种更优雅的方法来解决这个问题，但我想不出一种不使用过程的方法。这也可以拆分为可重复使用、可测试的部分。

$from = new DateTime($this->dateFrom);
$to = new DateTime($this->dateTo);
$period = new DatePeriod($from, DateInterval::createFromDateString('1 day'), $to);
$i = 0;
$sums = array();
// bind a new model for each "season" price
foreach ($period as $day) {
  $date = $day->format("Y-m-d");
  // create a new alias
  $alias = "ApartmentSeasonPrice$i";
  $params = array(
    'className' => 'ApartmentSeason',
    'foreign_key' => 'apartment_id',
    'conditions' => array(
      "'{$date}' BETWEEN $alias.date_from AND $alias.date_to",
      "$alias.date_to != " => $date
  );
  // attach the model
  $this->Apartment->bindModel(array(
    'hasOne' => array(
      $alias => $params;
    )
  ));
  // save the column we want to sum
  $sums[] = "$alias.price";
}
// total them up in a virtual field
$this->Apartment->virtualFields['rental_price'] = 'COALESCE(SUM('+implode(',', $sums).', 0))';
// paginate
$this->set('apartments', $this->paginate());