Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1800

Paginator results is wrong if your query use order by clause

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.2
    • Fix Version/s: 2.3, 2.3.1
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None
    • Environment:
      linux oracle

      Description

      NOTE: I didn't try this on other database, I'm using Oracle.

      if my original fetchJoin query use an order by clause, the results is not keeping the provided order by clause and re-order them by id.

      here is my generated query to get the distinct records that get generated:

      SELECT distinct ID0
      FROM
      (
      SELECT f0_.id AS ID0, f0_.deal_type_id AS DEAL_TYPE_ID1, f0_.title AS TITLE2, f0_.deal_date AS DEAL_DATE3, f0_.amount AS AMOUNT4,
      f0_.abstract AS ABSTRACT5, f0_.created_date AS CREATED_DATE6, f0_.last_updated_date AS LAST_UPDATED_DATE7,
      f0_.object_status_id AS OBJECT_STATUS_ID8, f0_.published_date AS PUBLISHED_DATE9, f0_.publishing_status_id AS PUBLISHING_STATUS_ID10,
      f1_.id AS ID11, f1_.role_id AS ROLE_ID12, f1_.role_type_id AS ROLE_TYPE_ID13, f2_.id AS ID14, f3_.id AS ID15, f4_.id AS ID16, f5_.id AS ID17,
      c6_.id AS ID18, d7_.id AS ID19
      FROM fo_deal f0_
      INNER JOIN fo_deal_role f1_ ON f0_.id = f1_.deal_id
      INNER JOIN fo_people f2_ ON f1_.people_id = f2_.id
      INNER JOIN fo_property_deal f3_ ON f0_.id = f3_.deal_id
      INNER JOIN fo_property f4_ ON f3_.property_id = f4_.id
      LEFT JOIN fo_property_asset f5_ ON f4_.id = f5_.property_id
      LEFT JOIN co_asset c6_ ON f5_.asset_id = c6_.id
      LEFT JOIN ds_record d7_ ON c6_.ds_id = d7_.id
      WHERE f1_.people_id = 2
      AND f0_.object_status_id <> 3
      AND f0_.publishing_status_id = 2
      ORDER BY f0_.deal_date DESC, f0_.published_date DESC
      )

      running this query I get the id 30, 44 when the inner query return 44, 30

      here is the query that should get generated to take care of the order by clause:
      SELECT distinct ID0, rownum+
      FROM
      (
      SELECT f0_.id AS ID0, f0_.deal_type_id AS DEAL_TYPE_ID1, f0_.title AS TITLE2, f0_.deal_date AS DEAL_DATE3, f0_.amount AS AMOUNT4,
      f0_.abstract AS ABSTRACT5, f0_.created_date AS CREATED_DATE6, f0_.last_updated_date AS LAST_UPDATED_DATE7,
      f0_.object_status_id AS OBJECT_STATUS_ID8, f0_.published_date AS PUBLISHED_DATE9, f0_.publishing_status_id AS PUBLISHING_STATUS_ID10,
      f1_.id AS ID11, f1_.role_id AS ROLE_ID12, f1_.role_type_id AS ROLE_TYPE_ID13, f2_.id AS ID14, f3_.id AS ID15, f4_.id AS ID16, f5_.id AS ID17,
      c6_.id AS ID18, d7_.id AS ID19
      FROM fo_deal f0_
      INNER JOIN fo_deal_role f1_ ON f0_.id = f1_.deal_id
      INNER JOIN fo_people f2_ ON f1_.people_id = f2_.id
      INNER JOIN fo_property_deal f3_ ON f0_.id = f3_.deal_id
      INNER JOIN fo_property f4_ ON f3_.property_id = f4_.id
      LEFT JOIN fo_property_asset f5_ ON f4_.id = f5_.property_id
      LEFT JOIN co_asset c6_ ON f5_.asset_id = c6_.id
      LEFT JOIN ds_record d7_ ON c6_.ds_id = d7_.id
      WHERE f1_.people_id = 2
      AND f0_.object_status_id <> 3
      AND f0_.publishing_status_id = 2
      ORDER BY f0_.deal_date DESC, f0_.published_date DESC
      ) ORDER BY rownum ASC

      To fix this on the Paginator code:

      file: ORM/Tools/Pagination/LimitSubqueryOutputWalker.php
      method: walkSelectStatement

      change:
      $sql = sprintf('SELECT b.*, rownum as rn FROM (SELECT DISTINCT %s FROM (%s)) b', // AS _dctrn_result',
      implode(', ', $sqlIdentifier), $sql);

      for:
      $sql = sprintf('SELECT b.*, rownum as rn FROM (SELECT DISTINCT %s, numrow FROM (%s) ORDER BY numrow ASC) b', // AS _dctrn_result',
      implode(', ', $sqlIdentifier), $sql);

        Activity

        Marc Drolet created issue -
        Benjamin Eberlei made changes -
        Field Original Value New Value
        Workflow jira [ 13664 ] jira-feedback [ 14056 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback [ 14056 ] jira-feedback2 [ 15920 ]
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Awaiting Testcase/Feedback [ 10000 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 15920 ] jira-feedback3 [ 18207 ]
        Benjamin Eberlei made changes -
        Status Awaiting Feedback [ 10000 ] In Progress [ 3 ]
        Benjamin Eberlei made changes -
        Status In Progress [ 3 ] Resolved [ 5 ]
        Fix Version/s 2.2.4 [ 10322 ]
        Fix Version/s 2.3 [ 10185 ]
        Resolution Fixed [ 1 ]
        Guilherme Blanco made changes -
        Fix Version/s 2.3.1 [ 10323 ]
        Fix Version/s 2.2.4 [ 10322 ]

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Marc Drolet
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: