Doctrine 1
  1. Doctrine 1
  2. DC-37

Performing JOIN and ORDERBY and using the PAGER in MSSQL results in bad query

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.4
    • Fix Version/s: 1.2.0-RC1
    • Component/s: Pager, Query, Relations
    • Labels:
      None
    • Environment:
      PHP 5.2.9-2, Apache, Microsoft SQL Server 2005, Windows XP SP 3

      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

        Activity

        Hide
        Daniel Cousineau added a comment -

        CORRECTION: Notice it was only when using the pager.

        Show
        Daniel Cousineau added a comment - CORRECTION: Notice it was only when using the pager.
        Hide
        Daniel Cousineau added a comment -
        Show
        Daniel Cousineau added a comment - Possibly related to http://www.doctrine-project.org/jira/browse/DC-36
        Hide
        Jonathan H. Wage added a comment -

        Are you using the latest 1.1.3?

        Show
        Jonathan H. Wage added a comment - Are you using the latest 1.1.3?
        Hide
        Felix-Johannes Jendrusch added a comment -

        Seems like I'm having the same issue using MSSQL, (Doctrine 1.1.3, Doctrine 1.2-ALPHA1), Zend_Paginator_Adapter_Doctrine and Doctrine::LIMIT_RECORDS (works with Doctrine::LIMIT_ROWS):

        SELECT
        	[c].[clientarchivefileid] AS [c__clientarchivefileid],
        	[c].[filename] AS [c__filename],
        	[c].[thumbnailimagename] AS [c__thumbnailimagename],
        	[c].[archivedate] AS [c__archivedate],
        	[c2].[identifier] AS [c2__identifier],
        	[c2].[archive] AS [c2__archive],
        	[c2].[trash] AS [c2__trash],
        	[c2].[name] AS [c2__name],
        	[f].[filetypeid] AS [f__filetypeid],
        	[f].[fileextension] AS [f__fileextension],
        	[c3].[clientarchivevideoserverid] AS [c3__clientarchivevideoserverid],
        	[c4].[identifier] AS [c4__identifier],
        	[c4].[url] AS [c4__url],
        	[c5].[videoserverid] AS [c5__videoserverid],
        	[c5].[videoservername] AS [c5__videoservername],
        	[l].[identifier] AS [l__identifier],
        	[l].[name] AS [l__name],
        	[t].[identifier] AS [t__identifier],
        	[t].[name] AS [t__name]
        FROM [ClientArchiveFile] [c]
        LEFT JOIN [ClientArchiveFileExtension] [c2]
        	ON [c].[clientarchivefileid] = [c2].[identifier]
        LEFT JOIN [FileType] [f]
        	ON [c].[filetypeid] = [f].[filetypeid]
        LEFT JOIN [ClientArchiveVideoServer] [c3]
        	ON [c].[clientarchivevideoserverid] = [c3].[clientarchivevideoserverid]
        LEFT JOIN [ClientArchiveVideoServerExtension] [c4]
        	ON [c3].[clientarchivevideoserverid] = [c4].[identifier]
        LEFT JOIN [ClientVideoServer] [c5]
        	ON [c3].[videoserverid] = [c5].[videoserverid]
        LEFT JOIN [ClientArchiveFileLabel] [c6]
        	ON ([c].[clientarchivefileid] = [c6].[fileidentifier])
        LEFT JOIN [Label] [l]
        	ON [l].[identifier] = [c6].[labelidentifier]
        LEFT JOIN [TerminalFile] [t2]
        	ON ([c].[clientarchivefileid] = [t2].[fileidentifier])
        LEFT JOIN [Terminal] [t]
        	ON [t].[identifier] = [t2].[terminalidentifier]
        WHERE
        	[c].[clientarchivefileid] IN (
        		SELECT
        			*
        		FROM (
        			SELECT TOP 10
        				*
        			FROM (
        				SELECT DISTINCT TOP 10
        					[c].[clientarchivefileid]
        				FROM [ClientArchiveFile] [c]
        				LEFT JOIN [ClientArchiveFileExtension] [c2]
        					ON [c].[clientarchivefileid] = [c2].[identifier]
        				LEFT JOIN [FileType] [f]
        					ON [c].[filetypeid] = [f].[filetypeid]
        				LEFT JOIN [ClientArchiveVideoServer] [c3]
        					ON [c].[clientarchivevideoserverid] = [c3].[clientarchivevideoserverid]
        				LEFT JOIN [ClientArchiveVideoServerExtension] [c4]
        					ON [c3].[clientarchivevideoserverid] = [c4].[identifier]
        				LEFT JOIN [ClientVideoServer] [c5]
        					ON [c3].[videoserverid] = [c5].[videoserverid]
        				LEFT JOIN [ClientArchiveFileLabel] [c6]
        					ON ([c].[clientarchivefileid] = [c6].[fileidentifier])
        				LEFT JOIN [Label] [l]
        					ON [l].[identifier] = [c6].[labelidentifier]
        				LEFT JOIN [TerminalFile] [t2]
        					ON ([c].[clientarchivefileid] = [t2].[fileidentifier])
        				LEFT JOIN [Terminal] [t]
        					ON [t].[identifier] = [t2].[terminalidentifier]
        				WHERE
        					[c].[filetypeid] = 1
        					AND (
        						[c2].[identifier] = NULL
        						OR (
        							[c2].[archive] = 0
        							AND [c2].[trash] = 0
        						)
        					)
        				ORDER BY
        					[c].[archivedate] DESC,
        					[l].[name] ASC
        			) AS [inner_tbl]
        			ORDER BY
        				[inner_tbl].[ ASC,
        				[inner_tbl].[ DESC
        		) AS [outer_tbl]
        		ORDER BY
        			[outer_tbl].[ desc,
        			[outer_tbl].[ asc
        	)
        	AND [c].[filetypeid] = 1
        	AND (
        		[c2].[identifier] = NULL 
        		OR (
        			[c2].[archive] = 0
        			AND [c2].[trash] = 0
        		)
        	)
        ORDER BY
        	[c].[archivedate] DESC,
        	[l].[name] ASC

        Probably a bug in Doctrine_Connection_Mssql::modifyLimitQuery()?

        Show
        Felix-Johannes Jendrusch added a comment - Seems like I'm having the same issue using MSSQL, (Doctrine 1.1.3, Doctrine 1.2-ALPHA1), Zend_Paginator_Adapter_Doctrine and Doctrine::LIMIT_RECORDS (works with Doctrine::LIMIT_ROWS ): SELECT [c].[clientarchivefileid] AS [c__clientarchivefileid], [c].[filename] AS [c__filename], [c].[thumbnailimagename] AS [c__thumbnailimagename], [c].[archivedate] AS [c__archivedate], [c2].[identifier] AS [c2__identifier], [c2].[archive] AS [c2__archive], [c2].[trash] AS [c2__trash], [c2].[name] AS [c2__name], [f].[filetypeid] AS [f__filetypeid], [f].[fileextension] AS [f__fileextension], [c3].[clientarchivevideoserverid] AS [c3__clientarchivevideoserverid], [c4].[identifier] AS [c4__identifier], [c4].[url] AS [c4__url], [c5].[videoserverid] AS [c5__videoserverid], [c5].[videoservername] AS [c5__videoservername], [l].[identifier] AS [l__identifier], [l].[name] AS [l__name], [t].[identifier] AS [t__identifier], [t].[name] AS [t__name] FROM [ClientArchiveFile] [c] LEFT JOIN [ClientArchiveFileExtension] [c2] ON [c].[clientarchivefileid] = [c2].[identifier] LEFT JOIN [FileType] [f] ON [c].[filetypeid] = [f].[filetypeid] LEFT JOIN [ClientArchiveVideoServer] [c3] ON [c].[clientarchivevideoserverid] = [c3].[clientarchivevideoserverid] LEFT JOIN [ClientArchiveVideoServerExtension] [c4] ON [c3].[clientarchivevideoserverid] = [c4].[identifier] LEFT JOIN [ClientVideoServer] [c5] ON [c3].[videoserverid] = [c5].[videoserverid] LEFT JOIN [ClientArchiveFileLabel] [c6] ON ([c].[clientarchivefileid] = [c6].[fileidentifier]) LEFT JOIN [Label] [l] ON [l].[identifier] = [c6].[labelidentifier] LEFT JOIN [TerminalFile] [t2] ON ([c].[clientarchivefileid] = [t2].[fileidentifier]) LEFT JOIN [Terminal] [t] ON [t].[identifier] = [t2].[terminalidentifier] WHERE [c].[clientarchivefileid] IN ( SELECT * FROM ( SELECT TOP 10 * FROM ( SELECT DISTINCT TOP 10 [c].[clientarchivefileid] FROM [ClientArchiveFile] [c] LEFT JOIN [ClientArchiveFileExtension] [c2] ON [c].[clientarchivefileid] = [c2].[identifier] LEFT JOIN [FileType] [f] ON [c].[filetypeid] = [f].[filetypeid] LEFT JOIN [ClientArchiveVideoServer] [c3] ON [c].[clientarchivevideoserverid] = [c3].[clientarchivevideoserverid] LEFT JOIN [ClientArchiveVideoServerExtension] [c4] ON [c3].[clientarchivevideoserverid] = [c4].[identifier] LEFT JOIN [ClientVideoServer] [c5] ON [c3].[videoserverid] = [c5].[videoserverid] LEFT JOIN [ClientArchiveFileLabel] [c6] ON ([c].[clientarchivefileid] = [c6].[fileidentifier]) LEFT JOIN [Label] [l] ON [l].[identifier] = [c6].[labelidentifier] LEFT JOIN [TerminalFile] [t2] ON ([c].[clientarchivefileid] = [t2].[fileidentifier]) LEFT JOIN [Terminal] [t] ON [t].[identifier] = [t2].[terminalidentifier] WHERE [c].[filetypeid] = 1 AND ( [c2].[identifier] = NULL OR ( [c2].[archive] = 0 AND [c2].[trash] = 0 ) ) ORDER BY [c].[archivedate] DESC, [l].[name] ASC ) AS [inner_tbl] ORDER BY [inner_tbl].[ ASC, [inner_tbl].[ DESC ) AS [outer_tbl] ORDER BY [outer_tbl].[ desc, [outer_tbl].[ asc ) AND [c].[filetypeid] = 1 AND ( [c2].[identifier] = NULL OR ( [c2].[archive] = 0 AND [c2].[trash] = 0 ) ) ORDER BY [c].[archivedate] DESC, [l].[name] ASC Probably a bug in Doctrine_Connection_Mssql::modifyLimitQuery() ?

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Daniel Cousineau
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: