[FIXED] CakePHP paginate turns values to null

Issue

I've got this function which gets all the "feedings" with their "feedingsupps". Within the funtion I calculate the sum of some fields:

/* FeedingTable.php */
public function feedings_with_totals($herd_id)
{
    $feedings = $this->find('all',[
            'contain' => [  
                'Herds',
                'Herds.Users',
                'Feedingsupps'
            ]
    ])
    ->where(['Feedings.herd_id'=>$herd_id])
    ->order(['Feedings.id'=>'desc']);

    foreach($feedings as $f)
    {
        $collection = new Collection($f->feedingsupps);
        $f->sum_weight = $collection->sumOf('weight');
        $f->sum_dryweight = $collection->sumOf('dryweight_total');
        $f->sum_price = $collection->sumOf('price_total');
    }
    return $feedings;
}

When I call this function and debug it, it looks as wanted:

[
    (int) 0 => object(App\Model\Entity\Feeding) {

        'id' => (int) 19,
        ...
        'sum_weight' => (float) 109,
        'sum_dryweight' => (float) 92.71,
        'sum_price' => (float) 17.1775,

Then I paginate it:

$feedings = $this->paginate($feedings);

After that the "sum" fields are turned to null:

[
    (int) 0 => object(App\Model\Entity\Feeding) {

        'id' => (int) 19,
        ...
        'sum_weight' => null,
        'sum_dryweight' => null,
        'sum_price' => null,

Why does the pagination do that?

Solution

The paginator doesn't modify your fields, what it does is running the query again, as applying the paginator options will put the query in a dirty state, and remove the possibly buffered result set. So the result set returned by the paginator is not the same result set that you are modifying in your method.

Even if what you're doing there would work, you shouldn't do it that way, as you will a) run the query twice, and b) unlike the paginator query you are fetching and processing all rows of the table (which can be a very expensive operation, and is unnecessary regardless).

Your calculations should be either in a result formatter, which is being applied once when the result set is requested:

$feedings->formatResults(function (\Cake\Collection\CollectionInterface $results) {
    return $results->map(function ($row) {
        $collection = collection($row['feedingsupps']);

        $row['sum_weight'] = $collection->sumOf('weight');
        $row['sum_dryweight'] = $collection->sumOf('dryweight_total');
        $row['sum_price'] = $collection->sumOf('price_total');

        return $row;
    });
});

or it should be done on SQL level:

$feedings
    ->select([
        'sum_weight' => $feedings->func()->sum('Feedingsupps.weight'),
        'sum_dryweight' => $feedings->func()->sum('Feedingsupps.dryweight_total'),
        'sum_price' => $feedings->func()->sum('Feedingsupps.price_total'),
    ])
    ->enableAutoFields(true)
    ->leftJoinWith('Feedingsupps')
    ->group('Feedings.id');

In both cases the calcuations will only be done for the records that are actually fetched, and the query is only being run once.

See also

• Cookbook > Database Access & ORM > Query Builder > Adding Calculated Fields
• Cookbook > Database Access & ORM > Query Builder > Using leftJoinWith
• Cookbook > Database Access & ORM > Query Builder > Using SQL Functions

Answered By - ndm