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

          People

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

            Dates

            • Created:
              Updated: