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.

Sign up for GitHub

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

Jump to bottom

Incorect count group by #322

Closed
laurentiustroia opened this issue Aug 10, 2015 · 9 comments
Closed

Incorect count group by #322

laurentiustroia opened this issue Aug 10, 2015 · 9 comments

Comments

@laurentiustroia
Copy link

I have follower configuration:

knp_paginator:
    page_range: 5                      # default page range used in pagination control
    default_options:
        page_name: page                # page query parameter name
        sort_field_name: sort          # sort field query parameter name
        sort_direction_name: direction # sort direction query parameter name
        distinct: true                 # ensure distinct results, useful when ORM queries are using GROUP BY statements
    template:
        pagination: NewzletAdminBundle:Common:paginator.html.twig

and follower query:

$query = $em->getRepository('SiteEntityBundle:Example')->createQueryBuilder('e')
->select('e')->groupBy('e.test')->getQuery();

$pagination = $paginator->paginate(
            $query,
            $request->query->get('page',1),
            10,
            array(
                'distinct' => true
            )
        );

but i have 10 result after group by and he give me totalCount is it 11 , but 11 is without groupBy. How can fix it ?
@ghost
Copy link

ghost commented Sep 11, 2015

Info: The totalCount is in Knp\Component\Pager\Pagination\AbstractPagination and added by Knp\Component\Pager\Paginator in paginate() method.

@ghost ghost mentioned this issue Sep 11, 2015
Closed
@laurentiustroia laurentiustroia mentioned this issue Sep 11, 2015
Closed
@faghihian71
Copy link

still waiting

@platinumgroupmedia
Copy link

platinumgroupmedia commented Aug 28, 2016 •
edited
Loading

Temp fix for this would be something like this:

`

$em = $this->getDoctrine()->getManager();
$dql_count = "SELECT COUNT(p) total FROM AppBundle:Product p  GROUP BY p.name ORDER BY p.created ASC";
$counts = $em->createQuery($dql_count)->getResult();

$query = $em->createQuery($dql)->setHint('knp_paginator.count', sizeof($counts));

`

This is the same query as I have for the pagination but it does a count instead. Then I use setHint() to give it the actual count.

@polc
Copy link
Contributor

polc commented Sep 15, 2017

Hello sorry for the late reponse, I was not able to reproduce your issues. Could you please provide the queries generated and your database version?

@maches
Copy link

maches commented Nov 14, 2017 •
edited
Loading

We have the same issue (maybe not).
This how we call paginator:
$paginator->paginate($program, $page, $perPage, ['wrap-queries' => true, 'distinct' => true]);

This is generated SQL with parameters:
First

SELECT COUNT(*) AS dctrn_count
FROM (SELECT
        p0_.id                         AS id_0,
        p0_.budget_places              AS budget_places_1,
        p0_.entrance_score             AS entrance_score_2,
        p0_.contract_places            AS contract_places_3,
        p0_.price                      AS price_4,
        p0_.foreign_price              AS foreign_price_5,
        p0_.foreign_places             AS foreign_places_6,
        p0_.military_department        AS military_department_7,
        p0_.exchange_study             AS exchange_study_8,
        p0_.double_diploma             AS double_diploma_9,
        p0_.duration                   AS duration_10,
        p0_.is_published               AS is_published_11,
        p0_.created_at                 AS created_at_12,
        p0_.updated_at                 AS updated_at_13,
        e1_.id                         AS id_14,
        e1_.trans_key                  AS trans_key_15,
        e2_.id                         AS id_16,
        e2_.trans_key                  AS trans_key_17,
        k3_.id                         AS id_18,
        k3_.trans_key                  AS trans_key_19,
        k4_.id                         AS id_20,
        k4_.trans_key                  AS trans_key_21,
        p5_.id                         AS id_22,
        p5_.is_foreign                 AS is_foreign_23,
        p5_.status                     AS status_24,
        p5_.created_at                 AS created_at_25,
        p5_.updated_at                 AS updated_at_26,
        u6_.id                         AS id_27,
        u6_.logo                       AS logo_28,
        u6_.photo                      AS photo_29,
        AsText(u6_.location_point)     AS location_point_30,
        u7_.id                         AS id_31,
        u7_.working_hours              AS working_hours_32,
        p8_.name                       AS name_33,
        p8_.url                        AS url_34,
        p8_.locale                     AS locale_35,
        p8_.id                         AS id_36,
        c9_.id                         AS id_37,
        u10_.full_name                 AS full_name_38,
        u10_.name                      AS name_39,
        u10_.short_name                AS short_name_40,
        u10_.description               AS description_41,
        u10_.promo_video               AS promo_video_42,
        u10_.locale                    AS locale_43,
        u10_.id                        AS id_44,
        u11_.address                   AS address_45,
        u11_.url                       AS url_46,
        u11_.reference_phone           AS reference_phone_47,
        u11_.reference_mail            AS reference_mail_48,
        u11_.selection_committee_phone AS selection_committee_phone_49,
        u11_.selection_committee_mail  AS selection_committee_mail_50,
        u11_.locale                    AS locale_51,
        u11_.id                        AS id_52,
        p12_.name                      AS name_53,
        p12_.short_name                AS short_name_54,
        p12_.url                       AS url_55,
        p12_.locale                    AS locale_56,
        p12_.id                        AS id_57
      FROM program p0_ LEFT JOIN education_form e1_ ON p0_.education_form_id = e1_.id
        LEFT JOIN education_level e2_ ON p0_.education_level_id = e2_.id
        LEFT JOIN knowledge_area k3_ ON p0_.knowledge_area_id = k3_.id
        LEFT JOIN knowledge_area_item k4_ ON p0_.knowledge_area_item_id = k4_.id
        LEFT JOIN program_partner p13_ ON p0_.id = p13_.program_id
        LEFT JOIN partner p5_ ON p5_.id = p13_.partner_id
        LEFT JOIN university u6_ ON p0_.university_id = u6_.id
        LEFT JOIN university_branch u7_ ON p0_.university_branch_id = u7_.id
        LEFT JOIN program_translation p8_ ON p0_.id = p8_.translatable_id
        LEFT JOIN city c9_ ON u7_.city_id = c9_.id
        LEFT JOIN university_translation u10_ ON u6_.id = u10_.translatable_id
        LEFT JOIN university_branch_translation u11_ ON u7_.id = u11_.translatable_id
        LEFT JOIN partner_translation p12_ ON p5_.id = p12_.translatable_id
        LEFT JOIN program_language p15_ ON p0_.id = p15_.program_id
        LEFT JOIN language l14_ ON l14_.id = p15_.language_id
      WHERE l14_.id IN (1, 2)
      GROUP BY e1_.id, e2_.id, k3_.id, k4_.id, p5_.id, u6_.id, u7_.id, c9_.id, p8_.id, u10_.id, u11_.id, p12_.id
      HAVING count(l14_.id) = 2
      ORDER BY p0_.is_published ASC, p0_.id DESC) dctrn_table;

Second

SELECT DISTINCT id_0
FROM (SELECT
        p0_.id                         AS id_0,
        p0_.budget_places              AS budget_places_1,
        p0_.entrance_score             AS entrance_score_2,
        p0_.contract_places            AS contract_places_3,
        p0_.price                      AS price_4,
        p0_.foreign_price              AS foreign_price_5,
        p0_.foreign_places             AS foreign_places_6,
        p0_.military_department        AS military_department_7,
        p0_.exchange_study             AS exchange_study_8,
        p0_.double_diploma             AS double_diploma_9,
        p0_.duration                   AS duration_10,
        p0_.is_published               AS is_published_11,
        p0_.created_at                 AS created_at_12,
        p0_.updated_at                 AS updated_at_13,
        e1_.id                         AS id_14,
        e1_.trans_key                  AS trans_key_15,
        e2_.id                         AS id_16,
        e2_.trans_key                  AS trans_key_17,
        k3_.id                         AS id_18,
        k3_.trans_key                  AS trans_key_19,
        k4_.id                         AS id_20,
        k4_.trans_key                  AS trans_key_21,
        p5_.id                         AS id_22,
        p5_.is_foreign                 AS is_foreign_23,
        p5_.status                     AS status_24,
        p5_.created_at                 AS created_at_25,
        p5_.updated_at                 AS updated_at_26,
        u6_.id                         AS id_27,
        u6_.logo                       AS logo_28,
        u6_.photo                      AS photo_29,
        AsText(u6_.location_point)     AS location_point_30,
        u7_.id                         AS id_31,
        u7_.working_hours              AS working_hours_32,
        p8_.name                       AS name_33,
        p8_.url                        AS url_34,
        p8_.locale                     AS locale_35,
        p8_.id                         AS id_36,
        c9_.id                         AS id_37,
        u10_.full_name                 AS full_name_38,
        u10_.name                      AS name_39,
        u10_.short_name                AS short_name_40,
        u10_.description               AS description_41,
        u10_.promo_video               AS promo_video_42,
        u10_.locale                    AS locale_43,
        u10_.id                        AS id_44,
        u11_.address                   AS address_45,
        u11_.url                       AS url_46,
        u11_.reference_phone           AS reference_phone_47,
        u11_.reference_mail            AS reference_mail_48,
        u11_.selection_committee_phone AS selection_committee_phone_49,
        u11_.selection_committee_mail  AS selection_committee_mail_50,
        u11_.locale                    AS locale_51,
        u11_.id                        AS id_52,
        p12_.name                      AS name_53,
        p12_.short_name                AS short_name_54,
        p12_.url                       AS url_55,
        p12_.locale                    AS locale_56,
        p12_.id                        AS id_57,
        l13_.id                        AS id_58
      FROM program p0_ LEFT JOIN education_form e1_ ON p0_.education_form_id = e1_.id
        LEFT JOIN education_level e2_ ON p0_.education_level_id = e2_.id
        LEFT JOIN knowledge_area k3_ ON p0_.knowledge_area_id = k3_.id
        LEFT JOIN knowledge_area_item k4_ ON p0_.knowledge_area_item_id = k4_.id
        LEFT JOIN program_partner p14_ ON p0_.id = p14_.program_id
        LEFT JOIN partner p5_ ON p5_.id = p14_.partner_id
        LEFT JOIN university u6_ ON p0_.university_id = u6_.id
        LEFT JOIN university_branch u7_ ON p0_.university_branch_id = u7_.id
        LEFT JOIN program_translation p8_ ON p0_.id = p8_.translatable_id
        LEFT JOIN city c9_ ON u7_.city_id = c9_.id
        LEFT JOIN university_translation u10_ ON u6_.id = u10_.translatable_id
        LEFT JOIN university_branch_translation u11_ ON u7_.id = u11_.translatable_id
        LEFT JOIN partner_translation p12_ ON p5_.id = p12_.translatable_id
        LEFT JOIN program_language p15_ ON p0_.id = p15_.program_id
        LEFT JOIN language l13_ ON l13_.id = p15_.language_id
      WHERE l13_.id IN (1, 2)
      GROUP BY e1_.id, e2_.id, k3_.id, k4_.id, p5_.id, u6_.id, u7_.id, c9_.id, p8_.id, u10_.id, u11_.id, p12_.id
      HAVING count(l13_.id) = 2) dctrn_result
ORDER BY is_published_11 ASC, id_0 DESC
LIMIT 12 OFFSET 0;

Third

SELECT
  p0_.id                         AS id_0,
  p0_.budget_places              AS budget_places_1,
  p0_.entrance_score             AS entrance_score_2,
  p0_.contract_places            AS contract_places_3,
  p0_.price                      AS price_4,
  p0_.foreign_price              AS foreign_price_5,
  p0_.foreign_places             AS foreign_places_6,
  p0_.military_department        AS military_department_7,
  p0_.exchange_study             AS exchange_study_8,
  p0_.double_diploma             AS double_diploma_9,
  p0_.duration                   AS duration_10,
  p0_.is_published               AS is_published_11,
  p0_.created_at                 AS created_at_12,
  p0_.updated_at                 AS updated_at_13,
  e1_.id                         AS id_14,
  e1_.trans_key                  AS trans_key_15,
  e2_.id                         AS id_16,
  e2_.trans_key                  AS trans_key_17,
  k3_.id                         AS id_18,
  k3_.trans_key                  AS trans_key_19,
  k4_.id                         AS id_20,
  k4_.trans_key                  AS trans_key_21,
  p5_.id                         AS id_22,
  p5_.is_foreign                 AS is_foreign_23,
  p5_.status                     AS status_24,
  p5_.created_at                 AS created_at_25,
  p5_.updated_at                 AS updated_at_26,
  u6_.id                         AS id_27,
  u6_.logo                       AS logo_28,
  u6_.photo                      AS photo_29,
  AsText(u6_.location_point)     AS location_point_30,
  u7_.id                         AS id_31,
  u7_.working_hours              AS working_hours_32,
  p8_.name                       AS name_33,
  p8_.url                        AS url_34,
  p8_.locale                     AS locale_35,
  p8_.id                         AS id_36,
  c9_.id                         AS id_37,
  u10_.full_name                 AS full_name_38,
  u10_.name                      AS name_39,
  u10_.short_name                AS short_name_40,
  u10_.description               AS description_41,
  u10_.promo_video               AS promo_video_42,
  u10_.locale                    AS locale_43,
  u10_.id                        AS id_44,
  u11_.address                   AS address_45,
  u11_.url                       AS url_46,
  u11_.reference_phone           AS reference_phone_47,
  u11_.reference_mail            AS reference_mail_48,
  u11_.selection_committee_phone AS selection_committee_phone_49,
  u11_.selection_committee_mail  AS selection_committee_mail_50,
  u11_.locale                    AS locale_51,
  u11_.id                        AS id_52,
  p12_.name                      AS name_53,
  p12_.short_name                AS short_name_54,
  p12_.url                       AS url_55,
  p12_.locale                    AS locale_56,
  p12_.id                        AS id_57,
  p0_.university_id              AS university_id_58,
  p0_.university_branch_id       AS university_branch_id_59,
  p0_.education_form_id          AS education_form_id_60,
  p0_.education_level_id         AS education_level_id_61,
  p0_.knowledge_area_id          AS knowledge_area_id_62,
  p0_.knowledge_area_item_id     AS knowledge_area_item_id_63,
  k4_.area_id                    AS area_id_64,
  p5_.logo_id                    AS logo_id_65,
  p5_.country_id                 AS country_id_66,
  u6_.main_branch_id             AS main_branch_id_67,
  u6_.slider_data_id             AS slider_data_id_68,
  u6_.social_links_id            AS social_links_id_69,
  u7_.university_id              AS university_id_70,
  u7_.city_id                    AS city_id_71,
  p8_.translatable_id            AS translatable_id_72,
  u10_.logo_id                   AS logo_id_73,
  u10_.translatable_id           AS translatable_id_74,
  u11_.translatable_id           AS translatable_id_75,
  p12_.translatable_id           AS translatable_id_76
FROM program p0_ LEFT JOIN education_form e1_ ON p0_.education_form_id = e1_.id
  LEFT JOIN education_level e2_ ON p0_.education_level_id = e2_.id
  LEFT JOIN knowledge_area k3_ ON p0_.knowledge_area_id = k3_.id
  LEFT JOIN knowledge_area_item k4_ ON p0_.knowledge_area_item_id = k4_.id
  LEFT JOIN program_partner p13_ ON p0_.id = p13_.program_id
  LEFT JOIN partner p5_ ON p5_.id = p13_.partner_id
  LEFT JOIN university u6_ ON p0_.university_id = u6_.id
  LEFT JOIN university_branch u7_ ON p0_.university_branch_id = u7_.id
  LEFT JOIN program_translation p8_ ON p0_.id = p8_.translatable_id
  LEFT JOIN city c9_ ON u7_.city_id = c9_.id
  LEFT JOIN university_translation u10_ ON u6_.id = u10_.translatable_id
  LEFT JOIN university_branch_translation u11_ ON u7_.id = u11_.translatable_id
  LEFT JOIN partner_translation p12_ ON p5_.id = p12_.translatable_id
  LEFT JOIN program_language p15_ ON p0_.id = p15_.program_id
  LEFT JOIN language l14_ ON l14_.id = p15_.language_id
WHERE l14_.id IN (1, 2) AND p0_.id IN (1266, 1265, 1264, 1261, 1255, 1254, 1252, 1160, 1158, 1156, 1154, 1153)
GROUP BY e1_.id, e2_.id, k3_.id, k4_.id, p5_.id, u6_.id, u7_.id, c9_.id, p8_.id, u10_.id, u11_.id, p12_.id
HAVING count(l14_.id) = 2
ORDER BY p0_.is_published ASC, p0_.id DESC;

First query get value 490 and we have 41 pages (12 per page), but real count is 97 and 9th page have 1 item, 10 and other to 41 are empty.

So the problem is actually in the First query

SELECT COUNT(*) AS dctrn_count FROM (SELECT p0_.id AS id_0, .....

If we use DISTINCT in it, it return correct value.

SELECT COUNT(DISTINCT(id_0)) AS dctrn_count FROM (SELECT p0_id AS id_0 .....

Sorry for big queries
Possibly related #389

@Millon15
Copy link

Have the same issue on mariadb-10.2.32. I gues this issue occurs only in mysql DBs.

@Millon15
Copy link

Fixed it by adding 'wrap-queries' => true to $options argument of the paginate function

@garak
Copy link
Collaborator

garak commented Jul 21, 2021

@laurentiustroia does the fix proposed by @Millon15 works for you?

@laurentiustroia
Copy link
Author

Hi @garak I was opened this issue long time ago, I don't remember what work around I was did on that time, so you can close this from my perspective if anyone else not have this problem anymore

@garak garak closed this as completed Aug 27, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
7 participants
@garak @polc @faghihian71 @laurentiustroia @maches @platinumgroupmedia @Millon15