你如何修改 CakePHP 3 中的 UNION 查询? [英] How do you modify a UNION query in CakePHP 3?

问题描述

我想在 CakePHP 3.0.0 中对联合查询进行分页.通过使用 自定义查找器，我让它几乎完美运行，但我找不到任何方法让 limit 和 offset 应用到联合中，而不是任何一个子查询.

I want to paginate a union query in CakePHP 3.0.0. By using a custom finder, I have it working almost perfectly, but I can't find any way to get limit and offset to apply to the union, rather than either of the subqueries.

换句话说，这段代码:

$articlesQuery = $articles->find('all');
$commentsQuery = $comments->find('all');
$unionQuery = $articlesQuery->unionAll($commentsQuery);
$unionQuery->limit(7)->offset(7); // nevermind the weirdness of applying this manually

产生这个查询:

(SELECT {article stuff} ORDER BY created DESC LIMIT 7 OFFSET 7)
UNION ALL 
(SELECT {comment stuff}) 

而不是我想要的，这是:

instead of what I want, which is this:

(SELECT {article stuff})
UNION ALL 
(SELECT {comment stuff})
ORDER BY created DESC LIMIT 7 OFFSET 7

我可以像这样手动构建正确的查询字符串:

I could manually construct the correct query string like this:

$unionQuery = $articlesQuery->unionAll($commentsQuery);
$sql = $unionQuery->sql();
$sql = "($sql) ORDER BY created DESC LIMIT 7 OFFSET 7";

但是我的自定义查找器方法需要返回一个 CakeDatabaseQuery 对象，而不是一个字符串.

but my custom finder method needs to return a CakeDatabaseQuery object, not a string.

所以，

• 有没有办法将 limit() 之类的方法应用于整个联合查询?
• 如果没有，有没有办法将 SQL 查询字符串转换为 Query 对象?

• Is there a way to apply methods like limit() to an entire union query?
• If not, is there a way to convert a SQL query string into a Query object?

注意:有 一个已关闭的问题 描述了与此类似的内容(除了使用 paginate($unionQuery)) 没有给出如何解决问题的建议.

Note: There's a closed issue that describes something similar to this (except using paginate($unionQuery)) without a suggestion of how to overcome the problem.

scrowler 好心地建议了这个选项，但我认为它行不通.如果 limit 设置为 5 并且完整的结果集是这样的:

scrowler kindly suggested this option, but I think it won't work. If limit is set to 5 and the full result set would be this:

Article 9     --|
Article 8       |
Article 7       -- Page One
Article 6       |
Article 5     --|

Article 4     --|
Comment 123     |
Article 3       -- Here be dragons
Comment 122     |
Comment 121   --|
...

然后第 1 页的查询将起作用，因为(前 5 篇文章)+(前 5 篇评论)，按日期手动排序，并修剪到组合结果的前 5 篇将导致文章 1-5.

Then the query for page 1 would work, because (the first five articles) + (the first five comments), sorted manually by date, and trimmed to just the first five of the combined result would result in articles 1-5.

但是第 2 页不起作用，因为 5 的 offset 将应用于文章和评论，这意味着前 5 条评论(未包括在内)在第 1) 页中，永远不会出现在结果中.

But page 2 won't work, because the offset of 5 would be applied to both articles and comments, meaning the first 5 comments (which weren't included in page 1), will never show up in the results.

推荐答案

不可能在 unionAll() 返回的查询上直接应用这些子句 AFAIK，它需要对 API 进行更改，以使编译器知道将 SQL 放在哪里，通过选项、新类型的查询对象等等.

Being able to apply these clauses directly on the query returned by unionAll() is not possible AFAIK, it would require changes to the API that would make the compiler aware where to put the SQL, being it via options, a new type of query object, whatever.

幸运的是，可以使用 将 SQL 附加到查询中Query::epilog()，即原始 SQL 片段

Luckily it's possible to append SQL to queries using Query::epilog(), being it raw SQL fragments

$unionQuery->epilog('ORDER BY created DESC LIMIT 7 OFFSET 7');

或查询表达式

$unionQuery->epilog(
    $connection->newQuery()->order(['created' => 'DESC'])->limit(7)->offset(7)
);

这应该会为您提供所需的查询.

This should give you the desired query.

应该注意的是，根据文档 Query::epilog() 需要一个字符串，或者一个具体的 CakeDatabaseExpressionInterface 形式的实现一个 CakeDatabaseExpressionQueryExpression 实例，不只是 any ExpressionInterface 实现，所以理论上后一个例子是无效的，即使查询编译器适用于任何 ExpressionInterface 实现.

It should be noted that according to the docs Query::epilog() expects either a string, or a concrete CakeDatabaseExpressionInterface implementation in the form a CakeDatabaseExpressionQueryExpression instance, not just any ExpressionInterface implementation, so theoretically the latter example is invalid, even though the query compiler works with any ExpressionInterface implementation.

也可以将联合查询用作子查询，这将使使用分页组件的上下文变得更容易，因为除了构建和注入子查询之外，您无需处理任何其他事情，因为paginator 组件将能够简单地在主查询上应用订单/限制/偏移量.

It's also possible to utilize the union query as a subquery, this would make things easier in the context of using the pagination component, as you wouldn't have to take care of anything other than building and injecting the subquery, since the paginator component would be able to simply apply the order/limit/offset on the main query.

/* @var $connection CakeDatabaseConnection */
$connection = $articles->connection();

$articlesQuery = $connection
    ->newQuery()
    ->select(['*'])
    ->from('articles');
    
$commentsQuery = $connection
    ->newQuery()
    ->select(['*'])
    ->from('comments');
    
$unionQuery = $articlesQuery->unionAll($commentsQuery);

$paginatableQuery = $articles
    ->find()
    ->from([$articles->alias() => $unionQuery]);

这当然也可以移到取景器中.

This could of course also be moved into a finder.

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