How do you paginate sub-queries in CakePHP 3?

Question

I have a CakePHP 3 application which has 2 models, Urls and Downloads. The models are associated such that a URL may have multiple downloads (Urls hasMany Downloads in Cake terminology).

When I do a query such as this it will return 1 row from the Urls table and all of the associated Downloads:

// This is in a view() method
$query = $this->Urls->find()->contain(['Downloads' => ['sort' => ['Downloads.created' => 'DESC'] ] ])->where(['id' => $id]);

I want to paginate the list of Downloads but can't see how to do this. I've taken a look at https://book.cakephp.org/3.0/en/controllers/components/pagination.html but the only thing I could find which I tried was:

public function initialize()
{
    parent::initialize();
    $this->loadComponent('Paginator');
}

// In my view() method
$this->paginate = [
    'contain' => ['Downloads']
];

$query = $this->Urls->find()->contain(['Downloads' => ['sort' => ['Downloads.created' => 'DESC'] ] ])->where(['id' => $id])->paginate();

But it just errors saying Unknown method "paginate"

Answer 1

Since you are showing just one Url you could do two queries.

one query to retrieve the url (without the downloads)

$url = $this->Urls->get($id);

the second one to retrieve (and paginate!) the dowloads

$downloads = $this->Urls->Downloads->find()
    ->sort(['created' => 'DESC'])
    ->where(['url_id' => $id]);

$downloads = $this->paginate($downloads) 

Answer 2

Since Downloads belongs to Urls, you could Do the opposite, like this:

$downloads = $this->Urls->Downloads->find()
    ->contain('Urls')
    ->where(['url_id' => $id])
    ->order(['created' => 'DESC']);

Answer 3

Your error is because paginate() is not a method of the table class and you are calling it on the table object, so it is unknown. You need to call it on the Controller object:-

$this->paginate();

As paginate is a controller method, it can't be called as part of the query for retrieving the Url as you are trying in your example. This means that you need to perform two queries, but this is what CakePHP would have down if you'd contained the downloads. For example:-

$this->Urls->get($id, ['contain' => 'Downloads']);

This essentially results in the following two queries as it can't be done in SQL using a JOIN:-

$this->Urls->get($id);
$this->Urls->Downloads->find('all')->where(['url_id' => $id])->all();

So, you need get the Url first:-

$url = $this->Urls->get($id);

And then to paginate the downloads you want to pass the find query for the downloads into your paginate method:-

$query = $this->Urls->Downloads->find()
    ->sort(['created' => 'DESC'])
    ->where(['url_id' => $id]);
$downloads = $this->paginate($query);