[FIXED] Can't use paginate on a query result

Issue

According to the doc http://book.cakephp.org/3.0/en/controllers/components/pagination.html, I'd like to paginate a result of a query like below:

$unlocked_sitesQuery = $this->Deviceconnections
    ->find()
    ->contain([
        'Agthemes.Sites',
        'Agthemes.Agpois',
        'Agthemes.Agthemelanguages'
    ])
    ->where(['request' => 'unlock'])
    ->groupBy('agtheme.site.id');

$unlocked_sites = $this->paginate($unlocked_sitesQuery);

But I get the following error:

Error: Call to undefined method ArrayIterator::alias() File /home/mywebsite/vendor/cakephp/cakephp/src/Controller/Component/PaginatorComponent.php
Line: 154

What does it mean?

EDIT It seems that @ndm is right but the doc says:

By default the paginate() method will use the default model for a controller. You can also pass the resulting query of a find method:

public function index() 
{
    $query = $this->Articles->find('popular')->where(['author_id' => 1]); 
    $this->set('articles', $this->paginate($query));
}

So it should work on a result set. Or I didn't understand what the doc explains. Possible.

Solution

It means that you are passing the wrong type of object. Pagination on result sets is not supported, only tables (either objects or names) and queries are.

groupBy is not a method of the query class, it's one of the magic methods that are causing the query to be executed, and forward the method call to the resulting result set. So you end up calling Cake\ORM\ResultSet::groupBy(), which returns another collection.

• Cookbook > ... ORM > Query Builder > Queries Are Collection Objects
• Cookbook > ... ORM > Retrieving Data & Results Sets > Working with Result Sets

So if you need such grouped results in pagination, then you have to solve this (at least partially) on SQL level, for example by fetching the results the other way around, ie fetch Sites and their associations, and filter by Deviceconnections.request, something like this (no guarantee that this will give you the desired result, but the example should give you a hint!):

$query = $Sites
    ->find()
    ->contain([
        'Agthemes.Deviceconnections',
        'Agthemes.Agpois',
        'Agthemes.Agthemelanguages'
    ])
    ->matching('Agthemes.Deviceconnections', function(\Cake\ORM\Query $query) {
        return $query
            ->where([
                'Deviceconnections.request' => 'unlock'
            ]);
    });

You'd of course have to adapt your view code accordingly.

Answered By - ndm