Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-82

orderBy(), setFirstResult() bug with MSSQL Server

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0.7, 2.1.1
    • Component/s: Platforms
    • Labels:
      None
    • Environment:
      Windows 7, Apache2, PHP 5.3.5, Microsoft SQL Server 2008

      Description

      QueryBuilder
      		$query = $this->createQueryBuilder('account')
      					  ->select('account')
      					  ->orderBy('account.id', 'DESC')
      					  ->getQuery();
      
                      $result = $query->setMaxResults($this->resultsPerPage+1)
              							->setFirstResult($this->offset)
              							->getResult();
      

      The above, when using "setFirstResult" and "orderBy" (like that), results in an error like so:

      SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "t0_.id" could not be bound.

      With a statement that looks something like so:

      WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY t0_.id DESC) AS "doctrine_rownum",   ...

      The reason the error is occurring is because you apparently need to use the "alias" (e.g, SELECT t0_.id AS id0) "id0" in the "ORDER BY" clause.

      So query will run with no problems if t0_.id as id0

      WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY id0 DESC) AS "doctrine_rownum",   ...

      It looks like this behavior might only occur with Microsoft SQL Server, but it is a bug.

      ---------------

      If you go into:
      Doctrine\DBAL\Platforms\MsSqlPlatform

      Find:

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

      Add before:

                          # Get Columns
                          $columns = array();
                          if(preg_match_all('/([a-zA-Z][0-9]+_\.[a-zA-Z0-9\-_]+)\sAS\s([a-zA-Z0-9\-\_]+)/', $query, $matched)) {
                              for($i=0; $i<count($matched[1]); ++$i)
                              {
                                  $columns[$matched[1][$i]] = $matched[2][$i];
                              }
                          }
                          
                          # Replace columns with their alias in the "orderby" statement
                          if(preg_match_all('/([a-zA-Z][0-9]+_\.[a-zA-Z0-9\-_]+)\s/i', $orderby, $matches)) {
                              foreach($matches[1] as $column) 
                              {
                                  $orderby = preg_replace('/'.$column.'/', $columns[$column], $orderby);
                              }
                          }
      

      Obviously this is a really ugly hack, but this resolves it.

        Activity

          People

          • Assignee:
            Juozas Kaziukenas
            Reporter:
            Aaron DM
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: