Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Count goes haywire when sorting translatable fields. #307

Open
Mondane opened this issue Apr 29, 2015 · 3 comments

Comments

@Mondane
Copy link

commented Apr 29, 2015

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

This comment has been minimized.

Copy link

commented Mar 3, 2016

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

This comment has been minimized.

Copy link
Author

commented Mar 21, 2017

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 #106 (comment)

@polc

This comment has been minimized.

Copy link
Collaborator

commented Sep 13, 2017

Any news @aderuwe ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.