您如何在CakePHP 3中分页子查询? [英] How do you paginate sub-queries in CakePHP 3?

问题描述

我有一个CakePHP 3应用程序，其中有2个模型，分别为Urls和Downloads.关联了这些模型，以便URL可能具有多个下载(在Cake术语中为Urls hasMany Downloads).

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).

当我执行这样的查询时，它将从Urls表中返回1行以及所有相关的Downloads:

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

我想对Downloads的列表进行分页，但是看不到该怎么做.我看过 https://book.cakephp.org /3.0/zh-CN/controllers/components/pagination.html ，但我唯一能找到的方法是:

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"

推荐答案

您的错误是因为paginate()不是表类的方法，并且您在表对象上调用它，所以它是未知的.您需要在Controller对象上调用它:-

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();

由于paginate是控制器方法，因此不能像在示例中尝试的那样将其作为检索Url的查询的一部分.这意味着您需要执行两个查询，但是如果您包含下载内容，这将是CakePHP要做的事情.例如:-

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

这实际上导致以下两个查询，因为使用JOIN在SQL中无法完成以下查询:-

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();

因此，您需要先获取Url:-

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);

这篇关于您如何在CakePHP 3中分页子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！