Skip to content

DDC-2621: Paginator with ORDER BY not working in MSSQL #3354

Closed
doctrinebot opened this Issue Aug 20, 2013 · 5 comments

2 participants

@doctrinebot

Jira issue originally created by user flip101:

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 usernamecanonical7
,g1_.email AS email8
,g1.email_canonical AS emailcanonical9
,g1_.enabled AS enabled10
,g1_.salt AS salt11
,g1_.password AS password12
,g1.last_login AS lastlogin13
,g1_.locked AS locked14
,g1_.expired AS expired15
,g1.expires_at AS expiresat16
,g1.confirmation_token AS confirmationtoken17
,g1.password_requested_at AS password_requestedat18
,g1_.roles AS roles19
,g1.credentials_expired AS credentialsexpired20
,g1.credentials_expire_at AS credentials_expireat21
,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

@doctrinebot

Comment created by flip101:

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).

@doctrinebot

Comment created by @beberlei:

Duplicate of DDC-2622

@doctrinebot

Issue was closed with resolution "Duplicate"

@doctrinebot

Comment created by flip101:

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

@doctrinebot

Comment created by @doctrinebot:

A related Github Pull-Request [GH-789] was closed:
#789

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.