Details
Bug
Closed
Major
Description
When adding an ORDER BY and a JOIN to a query when using MSSQL as the back end, an incomplete query is produced with duplicated ORDER BY sections.
$query = Doctrine_Query::create()
->from('User u')
->leftJoin('u.Roles')
->addOrderBy('u.lastname, u.firstname');
$pager = new Doctrine_Pager($query, $page, $per_page);
return array(
'pager' => $pager,
'results' => $pager->execute(),
);
Produces
SELECT [u].[id] AS [u__id], [u].[username] AS [u__username], [u].[password] AS [u__password], [u].[firstname] AS [u__firstname], [u].[lastname] AS [u__lastname], [u].[email] AS [u__email], [u].[phone] AS [u__phone], [u].[data] AS [u__data], [u].[created_at] AS [u__created_at], [u].[updated_at] AS [u__updated_at], [u].[deleted_at] AS [u__deleted_at], [r].[id] AS [r__id], [r].[name] AS [r__name] FROM [users] [u] LEFT JOIN [user_has_role] [u2] ON ([u].[id] = [u2].[user_id]) LEFT JOIN [role] [r] ON [r].[id] = [u2].[role_id] WHERE [u].[id] IN ( SELECT * FROM ( SELECT TOP 1 * FROM ( SELECT DISTINCT TOP 1 [u].[id] FROM [users] [u] LEFT JOIN [user_has_role] [u2] ON ([u].[id] = [u2].[user_id] ) LEFT JOIN [role] [r] ON [r].[id] = [u2].[role_id] ORDER BY [u].[lastname], [u].[firstname] ) AS [inner_tbl] ORDER BY [inner_tbl].[ DESC, [inner_tbl].[ DESC ) AS [outer_tbl] ORDER BY [outer_tbl].[ asc, [outer_tbl].[ asc) ORDER BY [u].[lastname], [u].[firstname]
Particularly, notice in the first ORDER BY blocks the incomplete clauses (e.g. [outer_tbl].[ )
*SCHEMA*
Role:
columns:
id:
primary: true
autoincrement: true
type: integer
notnull: true
name:
type: string(100)
notnull: true
relations:
Users:
foreignAlias: Roles
class: User
refClass: UserHasRole
UserHasRole:
columns:
user_id:
type: integer
primary: true
notnull: true
role_id:
type: integer
primary: true
notnull: true
relations:
User:
local: user_id
foreign: id
Role:
local: role_id
foreign: id
User:
tableName: users
actAs: [Timestampable, Softdelete]
columns:
id:
primary: true
autoincrement: true
type: integer
notnull: true
username:
type: string(255)
notnull: true
password:
type: string(255)
notnull: true
firstname:
type: string(255)
lastname:
type: string(255)
email:
type: string(255)
email: true
phone:
type: string(20)
data:
type: string
indexes:
user_index:
fields: [username]
type: unique
I will be digging around to find out what the problem is later, and start playing with the 1.2 branch to see if the problem exists there. If someone could help me and point me in a good direction to start looking that would be great
CORRECTION: Notice it was only when using the pager.