Paginating and sorting associated calculated fields

Need Help
1

Hello, I need to paginate and sort a list with Herds, days in, and how many animals are alive, sold or dead.

A Herds hasMany Animals and Animals belongsTo Herds
Herds fields: id, name, from_date, to_date
Animals fields: herd_id, status (1 for alive, -1 for sold and -3 for dead)

The exact same problem was discussed here but things have changed a lot since cakephp 1.

This is my ugly, current working solution:

$this->paginate = [
    'fields' => [
        'Herds.name',
        'days' => 'IF(Herds.to_date IS NULL, DATEDIFF(UTC_TIMESTAMP(), Herds.from_date), DATEDIFF(Herds.to_date, Herds.from_date))',
        'alive' => '(SELECT COUNT(*) FROM Animals WHERE Animals.status = 1 AND Animals.herd_id = Herds.id)',
        'sold' => '(SELECT COUNT(*) FROM Animals WHERE Animals.status = -1 AND Animals.herd_id = Herds.id)',
        'dead' => '(SELECT COUNT(*) FROM Animals WHERE Animals.status = -2 AND Animals.herd_id = Herds.id)',
    ],
]

I’ve been trying to solve it the right way, using contain and finders:

HerdsController.php
$tropas = $this->Herds->find()->contain('Deadanimals');

AnimalsTable.php
public function findDead(Query $query, array $options)
{
    return $query->select(['count' => 'COUNT(*)'])->where(['Animals.status' => -2]);
}

HerdsTable.php
$this->hasMany('Deadanimals')
    ->setClassName('Animals')
    ->setFinder('findDead')
    ->setForeignKey('herd_id')
    ->setBindingKey('id')
    ->setJoinType('INNER');

but I’m getting the error:

Unknown finder method “findDead” on App\Model\Table\AnimalsTable.

2

wouldn’t the custom finder findDead be identified in the association like this?

->setFinder('dead')
3

Yes, thanks! that was 1 problem.
I fixed it but I’m getting empty arrays on the property deadanimals when I should receive a number for each herd. I feel like I’m going on the wrong direction.

4

Yeah, probably the wrong direction.

You could just work your original code into a custom finder method method (or a chainable series of them) or you could use a CounterCache.

CounterCache will require schema changes, so you might not like that. But they do work great!

Either one would encapsulate your query logic and get it out of the controller.

//nice clean custom finder call
$result = $this->Herds->find('herdWithStatusCounts');

//or chained for a little reuse potential
$result = $this-Herds->find()
     ->where(['name' => 'Lazy D'])
     ->find(live)
     ->find(sold)
     ->find(dead);
5

Sorry for the late response. Thanks, I read about CounterCache a time ago but completely forgot about it.

I implemented it, it was very straightforward! Thanks again!