Paginating and sorting associated calculated fields

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' => [
        '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 =',
        'sold' => '(SELECT COUNT(*) FROM Animals WHERE Animals.status = -1 AND Animals.herd_id =',
        'dead' => '(SELECT COUNT(*) FROM Animals WHERE Animals.status = -2 AND Animals.herd_id =',

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

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

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


but I’m getting the error:

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

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


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.

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'])

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!