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

问题描述

我想在CakePHP 3.0.0中对联合查询进行分页。通过使用自定义查找器< a>，我几乎完全工作，但我找不到任何办法得到 limit 和 offset / code>以应用于联合，而不是任何一个子查询。

换句话说，此代码：

  $ articlesQuery = $ article-> find（'all'）; 
 $ commentsQuery = $ comments-> find（'all'）; 
 $ unionQuery = $ articleQuery-> unionAll（$ commentsQuery）; 
 $ unionQuery-> limit（7） - > offset（7）; //永远不要手动应用这个查询
  

产生这个查询：

 （SELECT {article stuff} ORDER BY created DESC LIMIT 7 OFFSET 7）
 UNION ALL 
（SELECT {comment stuff}）
 

而不是我想要的，这是：

 （SELECT {article stuff}）
 UNION ALL 
（SELECT {comment stuff}）
 ORDER BY created DESC LIMIT 7 OFFSET 7 
 可以手动构建正确的查询字符串，如下所示：
 $ b 
 
 $ b 
  $ unionQuery = $ articleQuery-> unionAll（$ commentsQuery）; 
 $ sql = $ unionQuery-> sql（）; 
 $ sql =（$ sql）ORDER BY created DESC LIMIT 7 OFFSET 7; 
  
但是我的自定义finder方法需要返回一个 \Cake \Database \ Query 对象，而不是字符串。
 
 
 
因此，
 
 
 
 
 
是否有一种方法可以将 limit（）应用到整个联合查询？
 
 
有一种方法可以将SQL查询字符串转换为Query对象？
 
 
 
 
 
注意：
有一个关闭的问题描述类似这样的东西（除了使用 paginate（$ unionQuery） 
 
 
对每个子查询应用限制和偏移量
 
 
 
 scrowler友好地建议这个选项，但我认为它不会工作。如果 limit 设置为5，并且完整结果集为：
 第9条 -  | 
第8条| 
第7条 - 第一页
第6条| 
第5条 -  | 
 
第4条 -  | 
评论123 | 
第3条 - 这里是龙
评论122 | 
注释121  -  | 
 ... 
  
然后，第1页的查询将工作，因为文章）+（前五个注释），按日期手动排序，并修剪为只有前五个组合结果将导致第1-5条。
 
 
 
 但第2页将不工作，因为 offset  5将应用于文章和注释，意味着前5个注释
解决方案
能够直接应用这些子句对 unionAll（）返回的查询不可能AFAIK，它将需要更改API，这将使编译器知道在哪里放置SQL， 
 
 
 
查询:: epilog（）到救援
 
 
 
幸运的是，可以使用   Query :: epilog（）  ，它是原始SQL片段
  $ unionQuery-> epilog（'ORDER BY created DESC LIMIT 7 OFFSET 7'）; 
  
或查询表达式
  $ unionQuery-> epilog（
 $ connection-> newQuery（） - > order（['created'=>'DESC']） - > limit（7） > offset（7）
）; 
  
这将给您所需的查询。
 
 
 
应该注意，根据docs  Query :: epilog（）需要一个字符串或一个具体的 \Cake\Database \ExpressionInterface 实现，形式为 \Cake\Database\Expression\QueryExpression 实例，而不仅仅是任何  ExpressionInterface 实现，所以理论上后面的例子是无效的，即使查询编译器使用任何 ExpressionInterface 实现。 
 
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.

In other words, this code:
$articlesQuery = $articles->find('all');
$commentsQuery = $comments->find('all');
$unionQuery = $articlesQuery->unionAll($commentsQuery);
$unionQuery->limit(7)->offset(7); // nevermind the weirdness of applying this manually
produces this query:
(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";
but my custom finder method needs to return a \Cake\Database\Query object, not a string.

So,


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?


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.

Apply limit and offset to each subquery?

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

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.
解决方案
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.

Query::epilog() to the rescue

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');
or query expressions
$unionQuery->epilog(
    $connection->newQuery()->order(['created' => 'DESC'])->limit(7)->offset(7)
);
This should give you the desired query.

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

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