doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-24 06:52:09 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

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

New Issue
Closed
opened 2026-01-22 14:17:21 +01:00 by admin · 10 comments
admin commented 2026-01-22 14:17:21 +01:00
Owner
Copy Link

Originally created by @doctrinebot on GitHub (Aug 20, 2013).

Originally assigned to: @Ocramius on GitHub.

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

Originally created by @doctrinebot on GitHub (Aug 20, 2013). Originally assigned to: @Ocramius on GitHub. 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`
admin added the Bug label 2026-01-22 14:17:21 +01:00
admin closed this issue 2026-01-22 14:17:22 +01:00
admin commented 2026-01-22 14:17:23 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 20, 2013):

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 commented on GitHub (Aug 20, 2013): 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).
admin commented 2026-01-22 14:17:23 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Sep 15, 2013):

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 commented on GitHub (Sep 15, 2013): 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.
admin commented 2026-01-22 14:17:23 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Sep 15, 2013):

Comment created by @ocramius:

Provided patches at https://github.com/doctrine/doctrine2/pull/789 and https://github.com/doctrine/dbal/pull/371

@doctrinebot commented on GitHub (Sep 15, 2013): Comment created by @ocramius: Provided patches at https://github.com/doctrine/doctrine2/pull/789 and https://github.com/doctrine/dbal/pull/371
admin commented 2026-01-22 14:17:23 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Sep 16, 2013):

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 commented on GitHub (Sep 16, 2013): Comment created by flip101: I don't understand your comment about filtering on fetch-join results being dangerous for hydration, could you please elaborate?
admin commented 2026-01-22 14:17:24 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Sep 16, 2013):

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 commented on GitHub (Sep 16, 2013): Comment created by @ocramius: [~flip101] it's unrelated to this change. I'd just explain that on IRC to avoid cluttering the issue here.
admin commented 2026-01-22 14:17:24 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Sep 16, 2013):

Comment created by @doctrinebot:

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

@doctrinebot commented on GitHub (Sep 16, 2013): Comment created by @doctrinebot: A related Github Pull-Request [GH-371] was closed: https://github.com/doctrine/dbal/pull/371
admin commented 2026-01-22 14:17:25 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jan 12, 2014):

Comment created by flip101:

Can be closed in favor of http://www.doctrine-project.org/jira/browse/DDC-2687

@doctrinebot commented on GitHub (Jan 12, 2014): Comment created by flip101: Can be closed in favor of http://www.doctrine-project.org/jira/browse/[DDC-2687](http://www.doctrine-project.org/jira/browse/DDC-2687)
admin commented 2026-01-22 14:17:25 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jan 12, 2014):

Comment created by @ocramius:

Handled in DDC-2687

@doctrinebot commented on GitHub (Jan 12, 2014): Comment created by @ocramius: Handled in [DDC-2687](http://www.doctrine-project.org/jira/browse/DDC-2687)
admin commented 2026-01-22 14:17:25 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Jan 12, 2014):

Issue was closed with resolution "Duplicate"

@doctrinebot commented on GitHub (Jan 12, 2014): Issue was closed with resolution "Duplicate"
admin commented 2026-01-22 14:17:28 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 4, 2014):

Comment created by @doctrinebot:

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

@doctrinebot commented on GitHub (Apr 4, 2014): Comment created by @doctrinebot: A related Github Pull-Request [GH-789] was closed: https://github.com/doctrine/doctrine2/pull/789
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#3290
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?