DBAL-1163: Pagination issue with order by statement #4426

New Issue

admin · 2026-01-22T14:41:23+01:00

admin commented

2026-01-22 14:41:23 +01:00

Originally created by @doctrinebot on GitHub (Mar 5, 2015).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user vaheshadunts:

For example I have 2 entities (family, person) with ManyToOne(familyId) association in Person entity, and OneToMany(mappedBy="familyId") in Family Entity (Full class definitions are in the end of description)

And this is my DQL part

    $query = $em -> createQuery('
        select e0, e1 from TestPagingBundle:Family e0 join e0.people e1
        order by e1.firstName asc
    ');
    $query -> setMaxResults(2);

This is working perfectly when the ordered firstNames are from different families.

But the paginator class generates 3 queries, 1st for fetching count, second for id's 3rd is a general query for data.

This is the 2nd Query:

SELECT DISTINCT id0, first_name3
FROM (
SELECT f0_.id AS id0, f0_.name AS name1, p1_.id AS id2, p1_.first_name AS first_name3, p1_.last_name AS last_name4
FROM family f0_
INNER JOIN person p1* ON f0_.id = p1_.family_id
ORDER BY p1_.first*name ASC
)dctrn_result
ORDER BY first_name3 ASC
LIMIT 2

So in the select statement in this query there are distinctly selected 2 fields, id and first_name.

And if we'll have 2 people in one family which names are Aaron and Abraham, this query result will be

id | name

1 | Aaron

1 | Abraham

So we have fetched only one id from families instead of 2, which we wanted to select.

Then the where statement of 3rd query will be where id in ( ? ) with params [1,1], and we are getting 1 Family instead of 2.

class Family
{
/****
* @var integer
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $id;

/****
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=256, nullable=true)
 */
private $name;

/****
 * @OneToMany(targetEntity="Person", mappedBy="familyId")
 ****/
private $people;

}

class Person
{
/****
* @var integer
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $id;

 /****
 * @ManyToOne(targetEntity="Family")
 * @JoinColumn(name="family_id", referencedColumnName="id")
 ****/
private $familyId;

/****
 * @var string
 *
 * @ORM\Column(name="first_name", type="string", length=256, nullable=false)
 */
private $firstName;

/****
 * @var string
 *
 * @ORM\Column(name="last_name", type="string", length=256, nullable=true)
 */
private $lastName;

}

Originally created by @doctrinebot on GitHub (Mar 5, 2015). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user vaheshadunts: For example I have 2 entities (family, person) with ManyToOne(familyId) association in Person entity, and OneToMany(mappedBy="familyId") in Family Entity (Full class definitions are in the end of description) And this is my DQL part ``` $query = $em -> createQuery(' select e0, e1 from TestPagingBundle:Family e0 join e0.people e1 order by e1.firstName asc '); $query -> setMaxResults(2); ``` This is working perfectly when the ordered firstNames are from different families. But the paginator class generates 3 queries, 1st for fetching count, second for id's 3rd is a general query for data. This is the 2nd Query: SELECT DISTINCT id0, first_name3 FROM ( SELECT f0_.id AS id0, f0_.name AS name1, p1_.id AS id2, p1_.first_name AS first_name3, p1_.last_name AS last_name4 FROM family f0_ INNER JOIN person p1\* ON f0_.id = p1_.family_id ORDER BY p1_.first*name ASC )dctrn_result ORDER BY first_name3 ASC LIMIT 2 So in the select statement in this query there are distinctly selected 2 fields, id and first_name. And if we'll have 2 people in one family which names are Aaron and Abraham, this query result will be ## id | name 1 | Aaron ## 1 | Abraham So we have fetched only one id from families instead of 2, which we wanted to select. Then the where statement of 3rd query will be where id in ( ? ) with params [1,1], and we are getting 1 Family instead of 2. --- class Family { /**** \* @var integer * \* @ORM\Column(name="id", type="integer") \* @ORM\Id \* @ORM\GeneratedValue(strategy="IDENTITY") */ private $id; ``` /**** * @var string * * @ORM\Column(name="name", type="string", length=256, nullable=true) */ private $name; /**** * @OneToMany(targetEntity="Person", mappedBy="familyId") ****/ private $people; ``` } class Person { /**** \* @var integer * \* @ORM\Column(name="id", type="integer") \* @ORM\Id \* @ORM\GeneratedValue(strategy="IDENTITY") */ private $id; ``` /**** * @ManyToOne(targetEntity="Family") * @JoinColumn(name="family_id", referencedColumnName="id") ****/ private $familyId; /**** * @var string * * @ORM\Column(name="first_name", type="string", length=256, nullable=false) */ private $firstName; /**** * @var string * * @ORM\Column(name="last_name", type="string", length=256, nullable=true) */ private $lastName; ``` }

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#4426