Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2236

SUM(..) with Pagination gives incorrect result

    Details

    • Type: Documentation Documentation
    • Status: In Progress
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.2.3
    • Fix Version/s: None
    • Component/s: Tools
    • Labels:
    • Environment:
      Linux

      Description

      https://github.com/whiteoctober/Pagerfanta/issues/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) dctrn_result 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_.customer_id
      SELECT q0_.id AS id21, SUM(q0_.price) AS sclr36 FROM Q q0_ WHERE q0_.id IN (19, 20, 22) GROUP BY q0_.customer_id

      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_.customer_id 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_.customer_id

        Activity

        Hide
        Marco Pivetta added a comment -

        Updating to Documentation issue.

        Show
        Marco Pivetta added a comment - Updating to Documentation issue.
        Hide
        Oleg added a comment -

        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?

        Show
        Oleg added a comment - 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?
        Hide
        Alexander added a comment -

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

        Show
        Alexander added a comment - Can you also test this with doctrine >= 2.3? The pagination code changed quite a lot.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Oleg
          • Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated: