Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2622

Paginator with ORDER BY not working in MSSQL

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Duplicate
    • Affects Version/s: 2.4
    • Fix Version/s: 2.4
    • Component/s: ORM, Tools
    • Security Level: All
    • Labels:
      None
    • Environment:
      SQL Server 2008 R2, Symfony 2.3 FOS User Bundle

      Description

      PHP code to test (A symfony 2.3 controller):

      <?php
      public function testAction() {
      	$em = $this->getDoctrine()->getManager();
      	$query = $em->createQuery("
      		SELECT report, user
      		FROM Report:Report report
      		
      		JOIN report.user user
      
      		WHERE user.id = ?1
      		ORDER BY report.created DESC
      	");
      	$query->setMaxResults(10);
      	$query->setParameter(1, 1);
      	$results = new Paginator($query, $fetchJoinCollection = true);
      	foreach ($results as $result) {}; // This was needed to trigger the query o_O
      
      	return new Response();
      }
      

      Schema:
      One User to Many Reports

      SQL + ERROR:

      An exception occurred while executing '
      SELECT *
      FROM (
      	SELECT DISTINCT id0
      		,ROW_NUMBER() OVER (
      			ORDER BY r0_.aangemaakt DESC
      			) AS doctrine_rownum
      	FROM (
      		SELECT r0_.id AS id0
      			,r0_.Naam AS Naam1
      			,r0_.Omschrijving AS Omschrijving2
      			,r0_.aangemaakt AS aangemaakt3
      			,r0_.gewijzigd AS gewijzigd4
      			,r0_.verwijderd AS verwijderd5
      			,g1_.username AS username6
      			,g1_.username_canonical AS username_canonical7
      			,g1_.email AS email8
      			,g1_.email_canonical AS email_canonical9
      			,g1_.enabled AS enabled10
      			,g1_.salt AS salt11
      			,g1_.password AS password12
      			,g1_.last_login AS last_login13
      			,g1_.locked AS locked14
      			,g1_.expired AS expired15
      			,g1_.expires_at AS expires_at16
      			,g1_.confirmation_token AS confirmation_token17
      			,g1_.password_requested_at AS password_requested_at18
      			,g1_.roles AS roles19
      			,g1_.credentials_expired AS credentials_expired20
      			,g1_.credentials_expire_at AS credentials_expire_at21
      			,g1_.id AS id22
      		FROM Rapporten r0_ WITH (NOLOCK)
      		INNER JOIN Gebruiker g1_ ON r0_.GebruikerId = g1_.id
      		WHERE (g1_.id = ?)
      			AND (r0_.verwijderd IS NULL)
      		) dctrn_result
      	) AS doctrine_tbl
      WHERE doctrine_rownum BETWEEN 1
      		AND 10
      ' with params [1]:
      
      SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The multi-part identifier "r0_.aangemaakt" could not be bound. 
      

      FIX:
      Change

      ORDER BY r0_.aangemaakt DESC

      to

      ORDER BY aangemaakt3 DESC

        Activity

        Hide
        Flip added a comment -

        I didn't get the Paginator working yet, but as i understand this is the first of 3 (maybe 2) queries, as described here: http://docs.doctrine-project.org/en/latest/tutorials/pagination.html It seems that in this first query it's not necessary to SELECT all these columns, so there is an opportunity here for a performance boost when not selecting them. (They still have to be selected in the final query to get the results).

        Show
        Flip added a comment - I didn't get the Paginator working yet, but as i understand this is the first of 3 (maybe 2) queries, as described here: http://docs.doctrine-project.org/en/latest/tutorials/pagination.html It seems that in this first query it's not necessary to SELECT all these columns, so there is an opportunity here for a performance boost when not selecting them. (They still have to be selected in the final query to get the results).
        Hide
        Marco Pivetta added a comment -

        Just a note: the DQL query you're doing here is very dangerous hydration-wise. Don't ever filter on fetch-joined results.

        Show
        Marco Pivetta added a comment - Just a note: the DQL query you're doing here is very dangerous hydration-wise. Don't ever filter on fetch-joined results.
        Show
        Marco Pivetta added a comment - Provided patches at https://github.com/doctrine/doctrine2/pull/789 and https://github.com/doctrine/dbal/pull/371
        Hide
        Flip added a comment -

        I don't understand your comment about filtering on fetch-join results being dangerous for hydration, could you please elaborate?

        Show
        Flip added a comment - I don't understand your comment about filtering on fetch-join results being dangerous for hydration, could you please elaborate?
        Hide
        Marco Pivetta added a comment -

        Flip it's unrelated to this change. I'd just explain that on IRC to avoid cluttering the issue here.

        Show
        Marco Pivetta added a comment - Flip it's unrelated to this change. I'd just explain that on IRC to avoid cluttering the issue here.
        Hide
        Doctrine Bot added a comment -

        A related Github Pull-Request [GH-371] was closed:
        https://github.com/doctrine/dbal/pull/371

        Show
        Doctrine Bot added a comment - A related Github Pull-Request [GH-371] was closed: https://github.com/doctrine/dbal/pull/371
        Hide
        Flip added a comment -
        Show
        Flip added a comment - Can be closed in favor of http://www.doctrine-project.org/jira/browse/DDC-2687
        Hide
        Marco Pivetta added a comment -

        Handled in DDC-2687

        Show
        Marco Pivetta added a comment - Handled in DDC-2687
        Hide
        Doctrine Bot added a comment -

        A related Github Pull-Request [GH-789] was closed:
        https://github.com/doctrine/doctrine2/pull/789

        Show
        Doctrine Bot added a comment - A related Github Pull-Request [GH-789] was closed: https://github.com/doctrine/doctrine2/pull/789

          People

          • Assignee:
            Marco Pivetta
            Reporter:
            Flip
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: