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.
Activity
Aaron DM
made changes -
| Field | Original Value | New Value |
|---|---|---|
| Description |
{code:title=QueryBuilder} $query = $this->createQueryBuilder('account') ->select('account') ->orderBy('account.id', 'DESC') ->getQuery(); $result = $query->setMaxResults($this->resultsPerPage+1) ->setFirstResult($this->offset) ->getResult(); {code} The above, when using "setFirstResult" and "orderBy" (like that), results in an error like so: {code}SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "t0_.id" could not be bound.{code} With a statement that looks something like so: {code}WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY t0_.id DESC) AS "doctrine_rownum", ...{code} 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 {code}WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY id0 DESC) AS "doctrine_rownum", ...{code} It looks like this behavior might only occur with Microsoft SQL Server, but it is a bug. |
{code:title=QueryBuilder} $query = $this->createQueryBuilder('account') ->select('account') ->orderBy('account.id', 'DESC') ->getQuery(); $result = $query->setMaxResults($this->resultsPerPage+1) ->setFirstResult($this->offset) ->getResult(); {code} The above, when using "setFirstResult" and "orderBy" (like that), results in an error like so: {code}SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "t0_.id" could not be bound.{code} With a statement that looks something like so: {code}WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY t0_.id DESC) AS "doctrine_rownum", ...{code} 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 {code}WITH outer_tbl AS (SELECT ROW_NUMBER() OVER (ORDER BY id0 DESC) AS "doctrine_rownum", ...{code} 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: {code}$over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);{code} Add before: {code} # 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); } } {code} Obviously this is a really ugly hack, but this resolves it. |
Benjamin Eberlei
made changes -
| Assignee | Benjamin Eberlei [ beberlei ] | Juozas Kaziukenas [ juokaz ] |
Benjamin Eberlei
made changes -
| Project | Doctrine 2 - ORM [ 10032 ] | Doctrine DBAL [ 10040 ] |
| Key | DDC-988 |
|
| Affects Version/s | 2.0 [ 10067 ] | |
| Affects Version/s | Git Master [ 10100 ] | |
| Component/s | Platforms [ 10056 ] | |
| Component/s | DQL [ 10014 ] |
Benjamin Eberlei
made changes -
| Status | Open [ 1 ] | Resolved [ 5 ] |
| Fix Version/s | 2.0.7 [ 10151 ] | |
| Fix Version/s | 2.1.1 [ 10156 ] | |
| Resolution | Fixed [ 1 ] |
Benjamin Eberlei
made changes -
| Workflow | jira [ 12306 ] | jira-feedback2 [ 17689 ] |
Benjamin Eberlei
made changes -
| Workflow | jira-feedback2 [ 17689 ] | jira-feedback3 [ 20044 ] |
This list may be incomplete, as errors occurred whilst retrieving source from linked applications:
- Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-82, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)
Added the ugly hack fix.