Skip to content

Loading…

DDC-2622: Paginator with ORDER BY not working in MSSQL #3355

Closed
doctrinebot opened this Issue · 10 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 *
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

@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 @ocramius:

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

@doctrinebot

Comment created by @ocramius:

Provided patches at #789 and doctrine/dbal#371

@doctrinebot

Comment created by flip101:

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

@doctrinebot

Comment created by @ocramius:

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

@doctrinebot

Comment created by @doctrinebot:

A related Github Pull-Request [GH-371] was closed:
doctrine/dbal#371

@doctrinebot

Comment created by @ocramius:

Handled in DDC-2687

@doctrinebot

Issue was closed with resolution "Duplicate"

@doctrinebot

Comment created by @doctrinebot:

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

@Ocramius Ocramius was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.4 milestone
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
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.