How to alter count() logic in Query/Builder/Paginator

Hello.

Using latest 2.10 build of Phalcon, the Phalcon\Paginator\Adapter\QueryBuilder cannot count distinct queries that contain joins correctly. This is fairly well reported, and fairly well ignored.

The common workaround seeming to be using a "group by" clause rather than distinct, however, I am in the process of migrating from mysql to sql server, and the group by solution in this case is fairly complex, due to its agregrate must be contained in the select list issue (i.e cannot group by on a single field, but rather requires all fields).

Having captured some queries, I can see when we use the Paginator for a Query builder that 2 sql queries are fired off. One is the query for the data, which works fine, the other is the count of that data, now this is incorrect nder certain circumstances.

So in this case, this is a query that has speciifed distinct, and joins to a further table that would cause duplication if its fields were not exluded from the results.

$paginator = new Phalcon\Paginator\Adapter\QueryBuilder(
                array(
                    "builder" => $searchUsersFilter->getQuery(),
                    "limit" => 20,
                    "page" => $pageNumber
                )
            );

Note the following queries have been simplified to table.* where the QueryBuilder would actually have build a query specifying every column involved.

The generated query might look like this (and provides the expected data) :

SELECT DISTINCT "_bentley_558800a168c66_users".*
FROM "_bentley_558800a168c66_users"  
INNER JOIN "userroles" AS "UserRole" ON "_bentley_558800a168c66_users"."userId" = "UserRole"."userId" 
WHERE ("_bentley_558800a168c66_users"."portalId" = 1011) AND ("UserRole"."roleId" <> 71)

The query returns 1699 unique results, which only contain fields belonging to the user records based on the select list.

Next the count query is fired off :

SELECT DISTINCT COUNT(*) AS "rowcount" FROM "_bentley_558800a168c66_users"  
INNER JOIN "userroles" AS "UserRole" ON "_bentley_558800a168c66_users"."userId" = "UserRole"."userId" 
WHERE ("_bentley_558800a168c66_users"."portalId" = 1011) AND ("UserRole"."roleId" <> 71)

This returns 1705 as the count which includes the count of 6 duplicate records. Obviously this ruins the pager.

The query is not structured correctly to deal with the filtered select list and the distinct requirement.

A better count query would use a sub query that more exactly represented the original query:

SELECT DISTINCT COUNT(*) AS "rowcount" 
FROM
(
    SELECT DISTINCT "_bentley_558800a168c66_users".* 
    FROM "_bentley_558800a168c66_users"
    INNER JOIN "userroles" AS "UserRole" ON "_bentley_558800a168c66_users"."userId" = "UserRole"."userId" 
    WHERE ("_bentley_558800a168c66_users"."portalId" = 1011) AND ("UserRole"."roleId" <> 71)
) as innerQ

Which returns the correct 1699 distinct row count which matches the data query.

So my question.

I want to override this count behaviour, when the QueryBuilder is passed to the Paginator, what class and what method is responsible for generating the count() query, and where can I best override it. The QueryBuilder appears to be mostly marked final.

Thanks