I'm utilizing a virtual area inside a model. That area signifies the sum of the several fields: SUM(Model.amount_1 + Model.amount_2 + ...). This is actually 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;

Which is the phrase the virtual area in CurrentTransfer model:

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

Let us the table doesn't have any records yet and also you use $this->paginate() in RecordsController to retrieve a listing of results, such as this:

class RecordsController extends AppController {

  var $name = 'Records';

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

  // ...

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

  // ...
}

I've discovered two different actions:

  • When the virtual area is really defined within the model it makes sense an assortment with one element storing a clear record, i.e. every area is placed to NULL.
  • When the virtual area isn't defined within the model it makes sense a clear array, the common behavior in CakePHP whenever a query returns a clear group of values.

I'd always expect the 2nd behavior, since is simpler to check if you will find results or otherwise.

Exactly why is CakePHP coming back a make believe result when you will find really no records within the table? Why adding virtual fields produce such behavior?

Thanks!

SUM() is definitely an aggregate function that covers all rows inside a column, not every posts consecutively while you most likely want. Being an aggregate function, it always returns something, a minimum of NULL. Which means the SQL query returns a minumum of one row with all of posts set to NULL. Cake is just picking this up.

I believe you are really searching for this:

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

I.e. the easy + operator with no SUM().