Doctrine 1
  1. Doctrine 1
  2. DC-279

Code Corrections For MsSql modifyLimitQuery

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.5
    • Fix Version/s: 1.2.0-RC1
    • Component/s: Connection
    • Labels:
      None

      Description

      Below are some code corrections for the modifyLimitQuery. These fixes should also fix DC-37

      Code details:

      • Fixed case on sorting orders to match all other syntax.
      • Added additional explode to ensure we only get fieldname and not tablename.fieldname.
      • Added code to determine primary key for use in select statement. There may be a better way of doing this, but not with the input sent to the function I don't think.
      • Removed the outer table (not sure how this could have ever worked) and switched inner table to return only primary key and not all fields.
      • Removed outer table order by logic.

      Diff - Old To New:

      160c160
      <                     $sorts[$i] = (stripos($orders[$i], ' desc') !== false) ? 'desc' : 'asc';
      ---
      >                     $sorts[$i] = (stripos($orders[$i], ' desc') !== false) ? 'DESC' : 'ASC';
      171a172
      >                     $aux2 = explode('.', end($aux2));
      185a187,191
      >             $fields_string = substr($query, strlen($selectReplace), strpos($query, ' FROM ') - strlen($selectReplace));
      >             $field_array = explode(',', $fields_string);
      >             $aux2 = explode('.', $field_array[0]);
      >             $key_field = trim(end($aux2));
      > 
      187c193
      <             $query = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $query . ') AS ' . $this->quoteIdentifier('inner_tbl');
      ---
      >             $query = 'SELECT TOP ' . $count . ' ' . $this->quoteIdentifier('inner_tbl') . '.' . $key_field . ' FROM (' . $query . ') AS ' . $this->quoteIdentifier('inner_tbl');
      199,212d204
      <                 }
      <             }
      < 
      <             $query .= ') AS ' . $this->quoteIdentifier('outer_tbl');
      < 
      <             if ($orderby !== false) {
      <                 $query .= ' ORDER BY '; 
      < 
      <                 for ($i = 0, $l = count($orders); $i < $l; $i++) { 
      <                     if ($i > 0) { // not first order clause 
      <                         $query .= ', '; 
      <                     } 
      < 
      <                     $query .= $this->quoteIdentifier('outer_tbl') . '.' . $aliases[$i] . ' ' . $sorts[$i];
      

      Entire New Function:

          public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false)
          {
              if ($limit > 0) {
                  $count = intval($limit);
                  $offset = intval($offset);
      
                  if ($offset < 0) {
                      throw new Doctrine_Connection_Exception("LIMIT argument offset=$offset is not valid");
                  }
      
                  $orderby = stristr($query, 'ORDER BY');
      
                  if ($orderby !== false) {
                      // Ticket #1835: Fix for ORDER BY alias
                      // Ticket #2050: Fix for multiple ORDER BY clause
                      $order = str_ireplace('ORDER BY', '', $orderby);
                      $orders = explode(',', $order);
      
                      for ($i = 0; $i < count($orders); $i++) {
                          $sorts[$i] = (stripos($orders[$i], ' desc') !== false) ? 'DESC' : 'ASC';
                          $orders[$i] = trim(preg_replace('/\s+(ASC|DESC)$/i', '', $orders[$i]));
      
                          // find alias in query string
                          $helper_string = stristr($query, $orders[$i]);
      
                          $from_clause_pos = strpos($helper_string, ' FROM ');
                          $fields_string = substr($helper_string, 0, $from_clause_pos + 1);
      
                          $field_array = explode(',', $fields_string);
                          $field_array = array_shift($field_array);
                          $aux2 = spliti(' as ', $field_array);
                          $aux2 = explode('.', end($aux2));
      
                          $aliases[$i] = trim(end($aux2));
                      }
                  }
      
                  // Ticket #1259: Fix for limit-subquery in MSSQL
                  $selectRegExp = 'SELECT\s+';
                  $selectReplace = 'SELECT ';
      
                  if (preg_match('/^SELECT(\s+)DISTINCT/i', $query)) {
                      $selectRegExp .= 'DISTINCT\s+';
                      $selectReplace .= 'DISTINCT ';
                  }
      
                  $fields_string = substr($query, strlen($selectReplace), strpos($query, ' FROM ') - strlen($selectReplace));
                  $field_array = explode(',', $fields_string);
                  $aux2 = explode('.', $field_array[0]);
                  $key_field = trim(end($aux2));
      
                  $query = preg_replace('/^'.$selectRegExp.'/i', $selectReplace . 'TOP ' . ($count + $offset) . ' ', $query);
                  $query = 'SELECT TOP ' . $count . ' ' . $this->quoteIdentifier('inner_tbl') . '.' . $key_field . ' FROM (' . $query . ') AS ' . $this->quoteIdentifier('inner_tbl');
      
                  if ($orderby !== false) {
                      $query .= ' ORDER BY '; 
      
                      for ($i = 0, $l = count($orders); $i < $l; $i++) { 
                          if ($i > 0) { // not first order clause 
                              $query .= ', '; 
                          } 
      
                          $query .= $this->quoteIdentifier('inner_tbl') . '.' . $aliases[$i] . ' '; 
                          $query .= (stripos($sorts[$i], 'asc') !== false) ? 'DESC' : 'ASC';
                      }
                  }
              }
      
              return $query;
          }
      

        Activity

        Hide
        Jonathan H. Wage added a comment -

        Thanks for the ticket and changes to the function.

        Show
        Jonathan H. Wage added a comment - Thanks for the ticket and changes to the function.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Michael Card
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: