Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2621

Paginator with ORDER BY not working in MSSQL

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Duplicate
    • Affects Version/s: 2.3.4
    • Fix Version/s: None
    • 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 DISTINCT TOP 10 id0
      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_
      INNER JOIN Gebruiker g1_ ON r0_.GebruikerId = g1_.id
      WHERE (g1_.id = ?)
      AND (r0_.verwijderd IS NULL)
      ORDER BY r0_.aangemaakt DESC
      ) dctrn_result
      ' with params [1]:

      SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

      FIX:
      Unknown

        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
        Benjamin Eberlei added a comment -

        Duplicate of DDC-2622

        Show
        Benjamin Eberlei added a comment - Duplicate of DDC-2622
        Hide
        Flip added a comment -

        It's not because, that one is on a different Doctrine version and the SQL looks differently as well.

        Show
        Flip added a comment - It's not because, that one is on a different Doctrine version and the SQL looks differently as well.
        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:
            Benjamin Eberlei
            Reporter:
            Flip
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: