Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting odd behavior from $query->setMaxResults()

Tags:

doctrine-orm

When I call setMaxResults on a query, it seems to want to treat the max number as "2", no matter what it's actual value is.

function findMostRecentByOwnerUser(\Entities\User $user, $limit)
{
    echo "2: $limit<br>";
    $query = $this->getEntityManager()->createQuery('
        SELECT t
        FROM Entities\Thread t
        JOIN t.messages m
        JOIN t.group g
        WHERE 
            g.ownerUser = :owner_user
        ORDER BY m.timestamp DESC
    ');
    $query->setParameter("owner_user", $user);
    $query->setMaxResults(4);
    echo $query->getSQL()."<br>";
    $results = $query->getResult();
    echo "3: ".count($results);
    return $results;
}

When I comment out the setMaxResults line, I get 6 results. When I leave it in, I get the 2 most recent results. When I run the generated SQL code in phpMyAdmin, I get the 4 most recent results. The generated SQL, for reference, is:

SELECT <lots of columns, all from t0_>
FROM Thread t0_ 
INNER JOIN Message m1_ ON t0_.id = m1_.thread_id 
INNER JOIN Groups g2_ ON t0_.group_id = g2_.id 
WHERE g2_.ownerUser_id = ? 
ORDER BY m1_.timestamp DESC 
LIMIT 4

Edit:

While reading the DQL "Limit" documentation, I came across the following:

If your query contains a fetch-joined collection specifying the result limit methods are not working as you would expect. Set Max Results restricts the number of database result rows, however in the case of fetch-joined collections one root entity might appear in many rows, effectively hydrating less than the specified number of results.

I'm pretty sure that I'm not doing a fetch-joined collection. I'm under the impression that a fetch-joined collection is where I do something like SELECT t, m FROM Threads JOIN t.messages. Am I incorrect in my understanding of this?

like image 445
Andrew Rueckert Avatar asked Sep 02 '11 22:09

Andrew Rueckert


1 Answers

An update : With Doctrine 2.2+ you can use the Paginator http://docs.doctrine-project.org/en/latest/tutorials/pagination.html

like image 97
Hervé Renault Avatar answered Oct 15 '22 17:10

Hervé Renault