Skip to content

Loading…

DDC-2236: SUM(..) with Pagination gives incorrect result #2935

Open
doctrinebot opened this Issue · 3 comments

2 participants

@doctrinebot

Jira issue originally created by user olegk:

whiteoctober/Pagerfanta#69

<?php
$query = $em->getRepository('M\E\Q')
->createQueryBuilder('q')
->select('q', 'SUM(q.price) AS amount')
->where('q.id IN(19, 20, 22)')
->groupBy('q.customer')
;

$pager = new Pagerfanta(new DoctrineORMAdapter($query));
$pager->setMaxPerPage(30);
$pager->setCurrentPage($request->query->get('page', 1));

$result = $pager->getCurrentPageResults();
print_r($result[0]['amount']); // 156.71 - Incorrect

$result = $query->getQuery()->getResult();
print_r($result[0]['amount']); // 553.47
?>

Sql for the above:

SELECT DISTINCT id0 FROM (SELECT q0.id AS id0, SUM(q0_.price) AS sclr36 FROM Q q0_ WHERE q0_.id IN (19, 20, 22) GROUP BY q0_.customer_id) dctrnresult LIMIT 30 OFFSET 0
SELECT q0.id AS id0, SUM(q0_.price) AS sclr36 FROM Q q0_ WHERE q0_.id IN (19, 20, 22) AND q0_.id IN ('19') GROUP BY q0_.customerid
SELECT q0.id AS id21, SUM(q0_.price) AS sclr36 FROM Q q0_ WHERE q0_.id IN (19, 20, 22) GROUP BY q0_.customerid

Sql with fetchJoin = false (new DoctrineORMAdapter($query, false))

SELECT q0.id AS id0, SUM(q0_.price) AS sclr36 FROM Quote q0_ WHERE q0_.id IN (19, 20, 22) GROUP BY q0_.customerid LIMIT 30 OFFSET 0
SELECT q0.id AS id0, SUM(q0_.price) AS sclr36 FROM Quote q0_ WHERE q0_.id IN (19, 20, 22) GROUP BY q0_.customerid

@doctrinebot

Comment created by @asm89:

Can you also test this with doctrine >= 2.3? The pagination code changed quite a lot.

@doctrinebot

Comment created by olegk:

Looks like no change

composer.json:

"doctrine/orm": "2.3.*",

php composer.phar update
Loading composer repositories with package information
Updating dependencies

  • Installing doctrine/common (2.3.0)
    Loading from cache

  • Installing doctrine/dbal (2.3.2)
    Loading from cache

then cleared cache but result is same
Here's the code

$query = $this->getDoctrine()->getEntityManager()->getRepository('MyBundle:Invoice')
  ->createQueryBuilder('q')
  ->select('q', 'SUM(q.amount) AS amount')
  ->groupBy('q.customer')
;
95 Connect  root@localhost on ****
95 Query    SELECT DISTINCT id0 FROM (SELECT i0*.id AS id0, i0_.invoice_num AS invoice_num1, i0_.date AS date2, i0_.amount AS amount3, i0_.vat_amount AS vat_amount4, i0_.amount_paid AS amount_paid5, i0_.md5 AS md56, i0_.is_exported AS is_exported7, i0_.created AS created8, SUM(i0_.amount) AS sclr9 FROM Invoice i0_ GROUP BY i0_.customer_id) dctrn*result LIMIT 30 OFFSET 0
95 Query    SELECT i0*.id AS id0, i0_.invoice_num AS invoice_num1, i0_.date AS date2, i0_.amount AS amount3, i0_.vat_amount AS vat_amount4, i0_.amount_paid AS amount_paid5, i0_.md5 AS md56, i0_.is_exported AS is_exported7, i0_.created AS created8, SUM(i0_.amount) AS sclr9, i0_.customer_id AS customer_id10 FROM Invoice i0_ WHERE i0_.id IN ('2') GROUP BY i0_.customer*id
95 Query    SELECT i0*.id AS id0, i0_.invoice_num AS invoice_num1, i0_.date AS date2, i0_.amount AS amount3, i0_.vat_amount AS vat_amount4, i0_.amount_paid AS amount_paid5, i0_.md5 AS md56, i0_.is_exported AS is_exported7, i0_.created AS created8, SUM(i0_.amount) AS sclr9, i0_.customer_id AS customer_id10 FROM Invoice i0_ GROUP BY i0_.customer*id
130210 16:08:25    95 Quit  

But I understand why that happens, it's due to group by and pagination nature.
The first query returns only one row with id "2", second query should be actually "..WHERE i0_.id IN ('2', '3', '4')"

If I do

$pager = new Pagerfanta(new DoctrineORMAdapter($query, false));

I get this sql

SELECT i0*.id AS id0, i0_.invoice_num AS invoice_num1, i0_.date AS date2, i0_.amount AS amount3, i0_.vat_amount AS vat_amount4, i0_.amount_paid AS amount_paid5, i0_.md5 AS md56, i0_.is_exported AS is_exported7, i0_.created AS created8, SUM(i0_.amount) AS sclr9, i0_.customer_id AS customer_id10 FROM Invoice i0* LIMIT 30 OFFSET 0

I think it should be noted somewhere that if you do groupBy you should set fetchJoin to false?

@doctrinebot

Comment created by @ocramius:

Updating to Documentation issue.

@beberlei beberlei was assigned by doctrinebot
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.