如何使用post计算字段对Cakephp查询进行排序并将其传递给paginate？

Question

Have a formatResults callback function that adds a "custom calculated" field into the entities post returned from a model query in my Cakephp. I would like to sort by this field and use this on a paginate is this possible?

So far i cannot accomplish this because the paginate limits the records fetched and therefore only records less than the paginator limit get sorted and not all the resultset...

Current code:

    $owners = $this->Owners->find('all');

    $owners->formatResults(function (\Cake\Collection\CollectionInterface $owners) {

        $owners = $owners->map(function ($entity) {

            $entity->random = rand(0,1);

            return $entity;

        });

        return $owners->sortBy(function ($item){

            return $item->random;

        },SORT_DESC);

    });

This works as expected:

$owners->toArray();

This does not:

$owners = $this->paginate($owners);
$owners->toArray();

Mainly because its "callback processing" only the first 10 records, i would like to process the whole resultset.

Answer 1

After diggin around ive found a similar topic opened by a previous user on the this link, it seems that is not possible to use pagination sort in other than the fields in the database.

As a result, i would suggest:

1 - Either alter your model logic, to accommodate your requirements by creating virtual fields or alter database schema to include this data.

2 - If the data requires further or live processing and it cannot be added or calculated in the database, perhaps programming a component that will replicate the paginate functionality on a cakephp collection would be a good option.The downside of this approach is that all records will be returned from the database which may present performance issues on large resultsets.