Count goes haywire when sorting translatable fields.

[Mondane]

If I order a paginator on a translatable field (done with ORMBehaviors\Translatable\Translation found on knplabs/doctrine-behaviors ), the totals are incorrect.

I have 16 items and 10 items per page, but this is what it says:

Page 1: 1 - 8 from 16 Page 2: 11 - 19 from 16

Where I expect this:

Page 1: 1 - 10 from 16 Page 2: 11 - 16 from 16

It works properly when sorting on a non-translatable field.

Could this have something to do with https://github.com/KnpLabs/KnpPaginatorBundle/blob/HEAD/Resources/doc/manual_counting.md ? If so, can't the count be done like this in the bundle:

From http://stackoverflow.com/questions/364825/getting-the-number-of-rows-with-a-group-by-query :

There is a nice solution in MySQL.

Add the keyword SQL_CALC_FOUND_ROWS right after the keyword SELECT :

SELECT SQL_CALC_FOUND_ROWS t3.id, a,bunch,of,other,stuff FROM t1, t2, t3 WHERE (associate t1,t2, and t3 with each other) GROUP BY t3.id LIMIT 10,20 After that, run another query with the function FOUND_ROWS() :

SELECT FOUND_ROWS(); It should return the number of rows without the LIMIT clause.

Checkout this page for more information : http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

or a subquery so all joins, group by's etc stay intact?

SELECT COUNT(*) FROM
( SELECT t3.id, a,bunch,of,other,stuff FROM t1, t2, t3 WHERE (associate t1,t2, and t3 with each other) GROUP BY t3.id )
as temp;

[aderuwe]

Experiencing this problem as well... When fetching the ID's (with DISTINCT), the sort field is included in the SELECT as well - this doesn't seem necessary as ordering will work even without selecting the sort field?

Any hints?

[Mondane]

I can't seem to reproduce this anymore. Currently using this set of bundles:

• "symfony/symfony": "3.1.*",
• "knplabs/knp-paginator-bundle": "~2.5",
• "knplabs/doctrine-behaviors": "~1.4",

Maybe they've fixed something.

@aderuwe can you reproduce this? Before I could test it though, I got this error when sorting:

Cannot select distinct identifiers from query with LIMIT and ORDER BY on a column from a fetch joined to-many association. Use output walkers.

It can be fixed by wrapping the queries in the paginator for count and distinct which, in my opinion, should be default behavior:

        $paginator = $this->get('knp_paginator');
        $pagination = $paginator->paginate(
            $items,
            $this->request->query->get('page', 1),
            $items_per_page,
            array(
                // To avoid problems with joins when doing a count or distinct,
                // wrap the queries in a subselect, see https://github.com/KnpLabs/KnpPaginatorBundle/issues/106#issuecomment-66918012
                'wrap-queries'=>true
            )
        );

See https://github.com/KnpLabs/KnpPaginatorBundle/issues/106#issuecomment-66918012

[polc]

Any news @aderuwe ?