Retrieve total count of paginated records along side custom limit in Cake PHP 2 paginator

Question

I'm working inside a legacy Cake PHP 2.10 application and am utilising both the Pagination component and PaginationHelper.

I've had to override the paginateCount method to set a custom option for my paginator settings to limit the maximum number of query results which are then paginated, limit and maxLimit are not sufficient on their own - I've added totalLimit.

The problem now is if there are less results than my totalLimit, such as when filtering, it continues to display 20,000 rather than the number of actual results. I could create another query specifically for this count, but wondered if there's a quick workaround that I'm missing, here's my my Application model method:

public function paginateCount($conditions = null, $recursive = 0, $extra = array())
{
        if (isset($extra['totalLimit'])) {
                return $extra['totalLimit'];
        }
}

And my pagination set up:

// define pagination settings
$this->Paginator->settings = array(
        'Application' => array(
                'paramType' => 'querystring',
                'totalLimit' => 20000,
                'limit' => $filters['pagination']['perPage'],
                'maxLimit' => $filters['pagination']['perPage'],
                'fields' => array(
                        'Application.*',
                        'ApplicationPayday.*',
                        'ApplicationApiLink.*',
                        'ApplicationResponse.*',
                        'AffiliateId.*',
                        'Redirect.*'
                ),
                'joins' => array(
                        array(
                                'table' => 'tlp_application_paydays',
                                'alias' => 'ApplicationPayday',
                                'type' => 'LEFT',
                                'conditions' => array(
                                        'ApplicationPayday.application_id = Application.id'
                                )
                        ),
                        array(
                                'table' => 'tlp_application_api_links',
                                'alias' => 'ApplicationApiLink',
                                'type' => 'LEFT',
                                'conditions' => array(
                                        'ApplicationApiLink.application_id = Application.id'
                                )
                        ),
                        array(
                                'table' => 'tlp_application_responses',
                                'alias' => 'ApplicationResponse',
                                'type' => 'LEFT',
                                'conditions' => array(
                                        'ApplicationResponse.application_id = Application.id'
                                )
                        ),
                        array(
                                'table' => 'tlp_affiliate_ids',
                                'alias' => 'AffiliateId',
                                'type' => 'LEFT',
                                'conditions' => array(
                                        'AffiliateId.aff_id = Application.tlp_aff_id'
                                )
                        ),
                        array(
                                'table' => 'tlp_redirects',
                                'alias' => 'Redirect',
                                'type' => 'LEFT',
                                'conditions' => array(
                                        'Redirect.application_id = Application.id'
                                )
                        )
                ),
                'conditions' => $queryConditions,
                'group' => array(
                        'Application.id'
                ),
                'order' => array(
                        'Application.id' => 'desc'
                ),
                'recursive' => -1
        )
);

// run query to get applications via paginated settings
try {
        $applications = $this->Paginator->paginate('Application');
} catch (\NotFoundException $e) {
        $this->Session->setFlash("Page doesn't exist. We've reset your search filters and taken you to the first page.");
        return $this->redirect('/payday_admin/leads/');
}

Answer 1

The problem is that you replace the number of total results that paginateCounts returns with $extra['totalLimit'] that you set (20.000 in this case).

The pagineCount() function should be overridden with something like in the example below. This way you will insert your total limit in the ->find('count', []) parameters and also keep the original count if your $extra['totalLimit'] parameter is not sent.

public function paginateCount($conditions, $recursive, $extra)
{
    if (isset($extra['totalLimit'])) {
        $limit = $extra['totalLimit'];
        unset($extra['totalLimit']);

        $count = $this->find('count', compact($conditions, $recursive, $limit, $extra));
        return (int)$count;
    }

    $count = $this->find('count', compact($conditions, $recursive, $extra));
    return (int)$count;
}

Your count will be limited to the maximum value of totalLimit, but will return the true count if it's lower.

If you have millions of rows with many joins I recommend caching the count. The cache key can be created by hashing the conditions and other parameters.

---

This is how the conditions are passed to the overridden paginateCount() from the model Class.

$this->Paginator->settings = [
    'limit' => 50,
    'contain' => [
        ...
    ],
    'conditions' => $conditions,
    'joins' => $joins,
    'group' => $group
];

$this->Paginator->paginate('Model');