Details
Bug
Resolved
Major
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.
