union request and pagination in cakephp4

Question

I made two requests. The first one gives me 2419 results and I store the result in $requestFirst. The second, 1 result and I store the result in $requestTwo. I make a union :

$requestTot = $requestFirst->union($requestTwo);

The total of the $requestTot is 2420 results so all is well so far. Then :

$request = $this->paginate($requestTot);
$this->set(compact('request'));

And here I don't understand, on each page of the pagination I find the result of $requestTwo. Moreover the pagination displays me :

Page 121 of 121, showing 20 record(s) out of 2,420 total

This is the right number of results except that when I multiply the number of results per page by the number of pages I get 2540. This is the total number of results plus one per page.

Can anyone explain?

Answer 1

Check the generated SQL in Debug Kit's SQL panel, you should see that the LIMIT AND OFFSET clauses are being set on the first query, not appended as global clauses so that they would affect the unionized query.

It will look something like this:

(SELECT id, title FROM a LIMIT 20 OFFSET 0)
UNION
(SELECT id, title FROM b)

So what happens then is that pagination will only be applied to the $requestFirst query, and the $requestTwo query will be unionized on top of it each and every time, hence you'll see its result on every single page.

A workaround for this current limitation would be to use the union query as a subquery or a common table expression from which to fetch the results. In order for this to work you need to make sure that the fields of your queries for the union are being selected without aliasing! This can be achieved by either using Table::subquery():

$requestFirst = $this->TableA
    ->subquery()
    ->select(['a', 'b'])
    // ...

$requestTwo = $this->TableB
    ->subquery()
    ->select(['c', 'd'])
    // ...

or by explicitly selecting the fields with aliases equal to the column names:

$requestFirst = $this->TableA
    ->find()
    ->select(['a' => 'a', 'b' => 'b'])
    // ...

$requestTwo = $this->TableB
    ->find()
    ->select(['c' => 'c', 'd' => 'd'])
    // ...

Then you can safely use those queries for a union as a subquery:

$union = $requestFirst->union($requestTwo);

$wrapper = $this->TableA
    ->find()
    ->from([$this->TableA->getAlias() => $union]);

$request = $this->paginate($wrapper);

or as a common table expression (in case your DBMS supports them):

$union = $requestFirst->union($requestTwo);

$wrapper = $this->TableA
    ->find()
    ->with(function (\Cake\Database\Expression\CommonTableExpression $cte) use ($union) {
        return $cte
            ->name('union_source')
            ->field(['a', 'b'])
            ->query($union)
    })
    ->select(['a', 'b'])
    ->from([$this->TableA->getAlias() => 'union_source']);

$request = $this->paginate($wrapper);