Paginate sort on subquery column in CakePHP 3

Question

This might be answered somewhere else already, but I was looking for an answer the past few days and couldn't find an answer that suited my problem/that I understood...

I'm using CakePHP 3.8.5 and am currently working on a query that includes a subquery in the select. I got 3 tables Locations, Computers and Printers. Locations and Computers are in a belongsToMany relationship, as well as Locations and Printers.

So I'm trying to get the following query, which is working well as far as the data results go:

    $computersQuery = $this->Computers->find();
    $computersQuery->select([$computersQuery->func()->count('*')])
        ->where(function (QueryExpression $exp) {
            return $exp
                ->notEq('Computers.Unwanted_Software', '')
                ->equalFields('Computers.Area_ID', 'Locations.Area_ID');
        });
        
    $printersQuery = $this->Printers->find();
    $printersQuery->select($printersQuery->func()->count('*'))
        ->where(function (QueryExpression $exp) {           
            return $exp
                ->eq('Printers.WHQL', 0)
                ->equalFields('Printers.Area_ID', 'Locations.Area_ID');
        });
    
    $dataQuery = $this->Locations->find();
    $dataQuery->select(['Locations.Area_ID',
            'Unwanted_Software' => $computersQuery,
            'Printers_Not_WHQL_Compatible' => $printersQuery])
        ->group('Locations.Area_ID');

So I'm trying to paginate the $dataQuery in my Controller. In my model I can click all three column headers but only the Area_ID column will get sorted. The two subquery columns won't sort. Even tho I'm not getting Errors. Looking at the SQL-log shows that it's never even trying to order by those two columns...

Any ideas how to fix this/work around this are highly appreciated!

If you need more info about my code just leave a comment below.

EDIT 1:

As user @ndm pointed out, I had to put the computed fields into the sortWhitelist option of the pagination array. Doing that worked out well as I was able to sort by the column headers:

    $this->paginate = [
        'limit' => '100',
        'sortWhitelist' => [
            'Locations.Area_ID',
            'Unwanted_Software',
            'Printers_Not_WHQL_Compatible'
        ],
        'order' => ['Locations.Area_ID' => 'ASC']
    ]

But then the next problem appeared. Was trying to sort by the Printers_Not_WHQL_Compatible column. The generated SQL code had one small issue (BTW I'm using SQL Server 2008):

    SELECT * 

    FROM (SELECT Locations.Area_ID AS [Locations__Area_ID], 
    (SELECT (COUNT(*)) FROM computers Computers WHERE (...)) AS [Unwanted_Software], 
    (SELECT (COUNT(*)) FROM printers Printers WHERE (...)) AS [Printers_Not_WHQL_Compatible], 
    (ROW_NUMBER() OVER (ORDER BY SELECT (COUNT(*)) FROM printers Printers WHERE (...) asc, Locations.Area_ID ASC)) AS [_cake_page_rownum_] FROM locations_Views Locations GROUP BY Locations.Area_ID ) _cake_paging_ 

    WHERE _cake_paging_._cake_page_rownum_ <= 100

This represents the generated SQL code. Problem is that in de Order By statement there are no brackets around my subquery. It should look like this, so that SQL Server can read it:

    ... ORDER BY ( SELECT (COUNT(*)) FROM printers Printers WHERE (...) ) asc, ...

Any ideas how to fix this?

EDIT 2:

So @ndm answer with either the pull-request or the fix via the newExpr() function both work great. At least regarding the parentheses for the subquery in the Order By.

Sadly already running into the next problem. The generated SQL (this counts for both solutions!) is kinda "refreshing" the parameter input for the entire query in the Order By, which means that it puts the filter parameters for the Where clauses in starting again by parameter :c0. You can see that in the following query:

SELECT * 

FROM (SELECT Locations.Area_ID AS [Locations__Area_ID], 
(SELECT (COUNT(*)) 
    FROM computers Computers 
    WHERE (Computers.Unwanted_Software != :c0 
    AND Computers.Area_ID = (Locations.Area_ID)
    )
) AS [Unwanted_Software], 
(SELECT (COUNT(*)) 
    FROM printers Printers 
    WHERE (Printers.WHQL = :c1 
    AND Printers.Area_ID = (Locations.Area_ID))
) AS [Printers_Not_WHQL_Compatible], 
(ROW_NUMBER() OVER (ORDER BY 
    (SELECT (COUNT(*)) 
        FROM printers Printers 
        WHERE (Printers.WHQL = :c0 
        AND Printers.Area_ID = (Locations.Area_ID))) asc,
    Locations.Area_ID ASC)
) AS [_cake_page_rownum_] 

FROM locations_Views Locations 

GROUP BY Locations.Area_ID ) _cake_paging_ 

WHERE _cake_paging_._cake_page_rownum_ <= 100

I don't think this is the intended result. I personally can probably work around that, by avoiding passing parameters directly (I don't have to deal with concrete external user input). Still think this should be looked over.

Thanks @ndm for great help! Definitely an upvote from me.

Answer 1

As linked in the comments, by default the paginator only allows sorting on columns that exist in the main table, columns of other tables (joins) or computed columns must be explicitly allowed via the sortWhiteList option.

The missing parentheses in the the generated window function SQL looks like a bug, the SQL Server query translator doesn't check whether the expression that is defined in the order clause is a query, which would require to wrap the generated SQL in parentheses.

I've pushed a possible fix for 3.9 and 4.1:

https://github.com/cakephp/cakephp/pull/15165
https://github.com/cakephp/cakephp/pull/15164

If you cannot upgrade right now, a possible workaround could be to wrap your subqueries in additional expressions, which when compiled should wrap the inner query expression in parentheses:

$dataQuery
    ->select([
        'Locations.Area_ID',
        'Unwanted_Software' => $dataQuery->newExpr($computersQuery),
        'Printers_Not_WHQL_Compatible' => $dataQuery->newExpr($printersQuery)
    ])
    ->group('Locations.Area_ID');