Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

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



60.9k
Accepted
answer
edited Jul '16

yea thats an intresting one :D

well probably the stupidiest idea ever, but let me embarrass myself :D

class toto extends \Phalcon\Paginator\Adapter\QueryBuilder {
    public function getPaginate(){

        return "yes i am here";
    }
}

$paginator = new toto(
            array(
                "builder" => koko::find(),
                "limit" => 20,
                "page" => 1
            )
        );

        echo "<pre>";
        var_dump($paginator->getPaginate()); //with print yes i am here
        echo "</pre>";
        exit;

now since this is working, you go here https://github.com/phalcon/cphalcon/blob/master/phalcon/paginator/adapter/querybuilder.zep#L111 and you write the function like it is in zep and inside you can fix whatever you want :-)

p.s. lazy man guide https://github.com/test-to-com/zep-to-php :D



60.9k
edited Jul '16

maybe we can cook a patch and let people send whatever query they like for total count .. i will think how can we do about that these days



1.4k

Hey Izo , that is not a stupid at all, and exactly what I needed!! Thank you so much :)

Let me figure out a hack that works for my immediate needs, and then patching with some custom count injection sounds like a great idea.

Once again, many thanks,



60.9k

here is the patch https://github.com/phalcon/cphalcon/pull/12041, in 3.0.1 it will be live.

when 3.0.0 is released ( probably in a week or so time ) just replace your file with this one https://github.com/Izopi4a/cphalcon/blob/PaginatorTotalCount/phalcon/paginator/adapter/querybuilder.zep and compile it than