分页和搜索与 CakePHP 3 中的动态 AJAX 加载相关的查询

Question

我正在开发一个CakePHP 3.7的应用程序。

我们有 3 个具有以下层次结构的数据库表。这些表根据表类正确关联：

• regulations
  • groups
    • filters

(关联显示在上一个问题中：CakePHP 3 - 未定义关联 - 即使它看起来是)

我可以从所有三个表中获取所有数据，如下所示：

$regulations = TableRegistry::getTableLocator()->get('Regulations');
$data = $regulations->find('all', ['contain' => ['Groups' => ['Filters']]]);
$data = $data->toArray();

filters表包含>1300 条记录。因此，我正在尝试构建一个功能，该功能在用户向下滚动页面时通过 AJAX 调用逐步加载数据，类似于此处描述的内容：https://makitweb.com/load-content-on-page-scroll-with-jquery-and-ajax/

问题是我需要能够计算返回的行总数。但是，此数据存在于 3 个表中。

如果我这样做debug($data->count());它将输出 8，因为regulations表中有 8 行。理想情况下，我需要它在filters表中返回的行数(~1300 行)，这是初始加载中存在的大部分数据的地方。

问题更加复杂，因为此功能允许用户执行搜索。可能是给定的搜索词存在于所有 3 个表中、1 - 2 个表中，或者根本不存在。我不知道正确的方法是尝试计算每个表中返回的行，还是整个行？

我已阅读 Cake 文档中的如何分页关联记录？和按关联数据筛选。

其他信息

问题似乎归结为如何使用 Cake 提供的 ORM 语法编写查询。以下(普通MySQL)查询实际上将执行我想要的操作。假设用户搜索了"石棉"：

SELECT
regulations.label AS regulations_label,
groups.label AS groups_label,
filters.label AS filters_label
FROM
groups
JOIN regulations
ON groups.regulation_id = regulations.id 
JOIN filters
ON filters.group_id = groups.id
WHERE regulations.label LIKE '%Asbestos%'
OR groups.label LIKE '%Asbestos%'
OR filters.label LIKE '%Asbestos%'
ORDER BY
regulations.id ASC,
groups_label ASC,
filters_label ASC
LIMIT 0,50

假设返回了 203 行。LIMIT条件意味着我得到了前 50 名。前端上的一些 js(就其工作方式而言并不真正相关)将在用户滚动到页面底部以不同的限制重新运行此查询时进行 ajax 调用(例如LIMIT 51, 100下一组结果)。

问题似乎是双重的：

1. 如果我使用 Cake 的 ORM 语法编写查询，则输出是一个嵌套数组。相反，如果我用普通 SQL 编写它，它会返回一个只有 3 列的表，其中包含我需要显示的标签。在页面上输出所需数据方面，此结构更容易使用。

2. 第二个 - 也许是更重要的 - 由于 1 中描述的嵌套结构，我看不出如何在 ORM 语法中编写LIMIT条件来使其工作。例如，如果我添加了$data->limit(50)，它只会对regulations表施加此限制。因此，从本质上讲，搜索适用于regulations前 50 行的任何关联数据。与我在 MySQL 中编写的LIMIT条件相反，该条件将考虑整个结果集，其中包括所有 3 个表中的列。

为了进一步阐述第 2 点，假设表包含以下行数：

• regulations： 150
• groups： 1000
• filters： 5000

如果我使用$data->limit(50)它仅适用于regulations表中的 50 行。我需要在搜索给定术语的所有 3 个表中的所有行后应用结果集LIMIT。

Answer 1

使用查询生成器创建该查询非常简单，如果您希望非 1：1 关联的连接，请使用*JoinWith()方法而不是contain()，在本例中为innerJoinWith()，如下所示：

$query = $GroupsTable
->find()
->select([
'regulations_label' => 'Regulations.label',
'groups_label' => 'Groups.label',
'filters_label' => 'Filters.label',
])
->innerJoinWith('Regulations')
->innerJoinWith('Filters')
->where([
'OR' => [
'Regulations.label LIKE' => '%Asbestos%',
'Groups.label LIKE' => '%Asbestos%',
'Filters.label LIKE' => '%Asbestos%',
],
])
->order([
'Regulations.id' => 'ASC',
'Groups.label' => 'ASC',
'Filters.label' => 'ASC',
]);

参见

• Cookbook> Database Access & ORM> Query Builder> Using innerJoinWith
• Cookbook> Database Access & ORM> Query Builder> Add Joins