Take the 2-minute tour ×

I'm looking for a better solution to solve this kind of problem. I need to paginate an advanced search for apartments with many search options: keyword, category, date from, date to, price range (price min, price max), and other fields ... In case you select the two dates I calculate the rental price of each apartment. To calculate the price I can not use a virtualField or one or more subqueries but I have to use a function of the model Apartment because it performs many calculations...

So I decided to use the callback afterFind to calculate the rental price and adding a "column" rental_price to the array of query results:

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

On controller:

$this->paginate = array( ... ); // Array with the query
$apartments = $this->paginate('Apartment');

foreach ($apartments as $key => &$apartment) {
   if($apartment['Apartment']['rental_price'] < $priceMin || $apartment['Apartment']['rental_price'] > $priceMax){
$this->set('apartments', $apartments);

The problem is that now I want to re-filter the array $apartments with minimum price and maximum price, but if I directly manipulating $apartments I have a problem with pagination. What's the best way to solve this kind of scenario?

   // 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;
share|improve this question
Elaborate on "have a problem with pagination" and add in the SQL that Cake generates when you try to paginate. –  jeremyharris Oct 4 '12 at 14:21
The problem is with paginateCount which does not return the correct number of pages / records –  manzapanza Oct 4 '12 at 14:35
Okay, can we see both the SQL that cake generates for the results and the count please? –  jeremyharris Oct 4 '12 at 14:37
I added the code and now it should be clear... because in SQL there is the condition that filters the results based on the rental_price. –  manzapanza Oct 4 '12 at 16:35
Well of course your pagination count will be off if you remove keys from the $apartments array. Those should be SQL conditions. Use BETWEEN. You should populate the calculations afterSave not afterFind in order to leverage them in the SQL query. –  jeremyharris Oct 4 '12 at 16:54
show 3 more comments

1 Answer

One way to solve this would be to bind a model for each price that falls within the date range, then total them up in a virtual field. There might be a more elegant way to solve this problem but I can't think of one that doesn't use procedures. This could also be split up into reusable, testable parts.

$from = new DateTime($this->dateFrom);
$to = new DateTime($this->dateTo);
$i = 0;
$sums = array();
// bind a new model for each "season" price
foreach ($period as $day) {
  // 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
    '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());
share|improve this answer
add comment

Your Answer


