How do you modify a UNION query in CakePHP 3?

Question

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.

Answer 1

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.

Use a subquery

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 \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 could of course also be moved into a finder.