Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-3029

DISTINCT , ORDER BY AND Limit in SQL Server

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      I don't know if I should report it here , becouse error is in SQLServerPlatform.php file.
      basicaly Distinct includes ROWNUM()
      So if you add distinct in DQL it will be translated to "select distinct ...... , Rownum()" - which is always distinct offcource.

      Here is original version
      protected function doModifyLimitQuery($query, $limit, $offset = null)
      {
      if ($limit > 0) {
      if ($offset == 0)

      { $query = preg_replace('/^(SELECT\s(DISTINCT\s)?)/i', '\1TOP ' . $limit . ' ', $query); }

      else {
      $orderby = stristr($query, 'ORDER BY');

      if ( ! $orderby)

      { $over = 'ORDER BY (SELECT 0)'; }

      else

      { $over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby); }

      // Remove ORDER BY clause from $query
      $query = preg_replace('/\s+ORDER BY(.*)/', '', $query);
      $query = preg_replace('/^SELECT\s/', '', $query);

      $start = $offset + 1;
      $end = $offset + $limit;

      $query = "SELECT * FROM (SELECT ROW_NUMBER() OVER ($over) AS doctrine_rownum, $query) AS doctrine_tbl WHERE doctrine_rownum BETWEEN $start AND $end";
      }
      }

      return $query;
      }
      In Attachenment there is a fixed version of this file.

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Michał Banaś
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: