Pagination in CakePHP with virtual fields does not return an empty array when there are no results

I am using a virtual field in a model. That field represents the sum of several fields: SUM(Model.amount_1 + Model.amount_2 + ...). This is the schema:

create table `current_transfers` (
  `id` integer unsigned not null auto_increment primary key,
  `description` varchar(1024) not null,
  `product_amount` decimal(13,2) default 0.0 not null,
  `tax_amount` decimal(13,2) default 0.0 not null,
  `other_amount` decimal(13,2) default 0.0 not null,
  `record_id` integer unsigned not null,
  constraint foreign key (`record_id`) references `records`(`id`) on delete cascade,
) ENGINE=InnoDB;

And this is the definition of the virtual field in CurrentTransfer model:

var $virtua开发者_开发问答lFields = array(
  'amount' => 'SUM(
    CurrentTransfer.product_amount +
    CurrentTransfer.tax_amount +
    CurrentTransfer.other_amount)'
);

Let's say the table does not have any records yet and you use $this->paginate() in RecordsController to retrieve a list of results, like this:

class RecordsController extends AppController {

  var $name = 'Records';

  var $paginate = array(
    'CurrentTransfer' => array(
      'order' => array('CurrentTransfer.amount' => 'desc'),
    ),
  );

  // ...

  public function view($id) {
    // ...
    $this->paginate('CurrentTransfer', array('CurrentTransfer.record_id' => $id));
    // ...
  }

  // ...
}

I have found two different behaviors:

• If the virtual field is actually defined in the model the result is an array with one element storing an empty record, i.e. every field is set to NULL.
• If the virtual field is not defined in the model the result is an empty array, which is the common behavior in CakePHP when a query returns an empty set of values.

I would always expect the second behavior, since is easier to compare if there are results or not.

Why is CakePHP returning a fictitious result when there are actually no records in the table? Why the addition of virtual fields produce such behavior?

SUM() is an aggregate function that sums up all rows in a column, not all columns in a row as you probably want. As an aggregate function, it always returns a value, at least NULL. That means the SQL query returns at least one row with all columns set to NULL. Cake is simply picking this up.

I think you're actually looking for this:

public $virtualFields = array(
  'amount' => 'CurrentTransfer.product_amount + CurrentTransfer.tax_amount + CurrentTransfer.other_amount'
);

I.e. the simple + operator without the SUM().