How do you modify a UNION query in CakePHP 3? -

i want paginate union query in cakephp 3.0.0. using custom finder, have working almost perfectly, can't find way limit , offset apply union, rather either of subqueries.

in other words, code:

$articlesquery = $articles->find('all'); $commentsquery = $comments->find('all'); $unionquery = $articlesquery->unionall($commentsquery); $unionquery->limit(7)->offset(7); // nevermind weirdness of applying manually 

produces query:

(select {article stuff} order created desc limit 7 offset 7) union  (select {comment stuff})  

instead of want, this:

(select {article stuff}) union  (select {comment stuff}) order created desc limit 7 offset 7 

i could manually construct correct query string this:

$unionquery = $articlesquery->unionall($commentsquery); $sql = $unionquery->sql(); $sql = "($sql) order created desc limit 7 offset 7"; 

but custom finder method needs return \cake\database\query object, not string.

so,

• is there way apply methods limit() entire union query?
• if not, there way convert sql query string query object?

note: there's a closed issue describes similar (except using paginate($unionquery)) without suggestion of how overcome problem.

apply limit , offset each subquery?

scrowler kindly suggested option, think won't work. if limit set 5 , full result set this:

article 9     --| article 8       | article 7       -- page 1 article 6       | article 5     --|  article 4     --| comment 123     | article 3       -- here dragons comment 122     | comment 121   --| ... 

then query page 1 work, because (the first 5 articles) + (the first 5 comments), sorted manually date, , trimmed first 5 of combined result result in articles 1-5.

but page 2 won't work, because offset of 5 applied both articles , comments, meaning first 5 comments (which weren't included in page 1), never show in results.

being able apply these clauses directly on query returned unionall() not possible afaik, require changes api make compiler aware put sql, being via options, new type of query object, whatever.

query::epilog() rescue

luckily it's possible append sql queries using query::epilog(), being raw sql fragments

$unionquery->epilog('order created desc limit 7 offset 7'); 

or query expressions

$unionquery->epilog(     $connection->newquery()->order(['created' => 'desc'])->limit(7)->offset(7) ); 

this should give desired query.

it should noted according docs query::epilog() expects either string, or concrete \cake\database\expressioninterface implementation in form \cake\database\expression\queryexpression instance, not any expressioninterface implementation, theoretically latter example invalid, though query compiler works expressioninterface implementation.

use subquery

it's possible utilize union query subquery, make things easier in context of using pagination component, wouldn't have take care of other building , injecting subquery, since paginator component able apply order/limit/offset on main query.

/* @var $connection \cake\database\connection */ $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 of course moved finder.