Limiting a doctrine query with a fetch-joined collection?

Question

I have a doctrine query that returns blog posts and their comments:

SELECT b, c FROM BlogPost b LEFT JOIN b.comments c

I would like to limit the results to 10 blog posts. According to the DQL documentation, setMaxResults() doesn't work correctly on queries that fetch-join a collection (comments in this case):

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.

How would I properly limit a doctrine query that contains a fetch-joined collection (in this case, limit the results to 10 blog posts)?

score 35 · Accepted Answer · edited Apr 04 '14 at 20:20

35

Paginate was merged with doctrine 2.2 And the new symfony2 release 2.0.10 is compatible with.

Now use it like that

//use Doctrine paginator
use Doctrine\ORM\Tools\Pagination\Paginator;

Write your query then call results like that.

$query->setMaxResults($limit);
$query->setFirstResult($offset);
$results = new Paginator($query, $fetchJoin = true);

Hope this will help you.

Note: If you are using SF2 2.0.10, you should update the deps and deps.lock files and specify the 2.2 version for Doctrine bundles.

edited Apr 04 '14 at 20:20

BenMorel

34,448
50
182
322

answered Feb 23 '12 at 17:02

Rmannn

1,335
1
13
18

1

A note here, `foreach ($results as $post) { echo $post "\n"; }` `$post` is each row of the data. – Jason Liu Oct 30 '18 at 17:22

score 3 · Answer 2 · answered Apr 12 '11 at 16:40

3

This repository http://github.com/beberlei/DoctrineExtensions has a paginator extension that works with fetch joins. You have to essentially make 3 SELECT statements, all of which this extension does for you.

answered Apr 12 '11 at 16:40

beberlei

4,297
1
23
25

Thanks. Do you mind sharing how to do this without the DoctrineExtensions? Right now, I do not want to include any extra library that isn't absolutely needed. I also think this would benefit many other readers in the future. – Stephen Watkins Apr 12 '11 at 16:55
You should look at the code of the extension. Its: First: One SELECT DISTINCT mainentity.id WHERE clause to get all the ids. Second: One Select Clause with WHERE id IN (..) with the previously fetched ids. Third: one SELECT DISTINCT clause for the number of total results. – beberlei Apr 13 '11 at 16:06
Thanks, I understand what's going on now. I did end up using the extensions though. Looked like a lot of useful extensions with little overhead. – Stephen Watkins Apr 13 '11 at 17:22

HMR · Answer 3 · 2017-01-13T03:34:06.180

Even though this is an old one this scores high on google looking for doctrine left join limit. Quickly it got me looking for "doctrine paginator" and after insisting to google that that was what I was looking for (not doctrine pagination) I still had some trouble figuring out how to use it.

Maybe searched wrong but could not really find good documentation on the paginator object. Anyway; to get the results I used getIterator and that worked fine.

My code has Sources that contain rss links and Articles that are articles from the rss feed. So in this example I'll get one Source and all it's articles. This code is from Symfony.

        // get the articles (latest first) from source 743
        $q=$this->getDoctrine()->getManager()
          ->createQuery('select s, a from MyCompanyRssBundle:Source s 
          join s.Articles a 
          where s.id = :id 
          order by a.id desc')
          ->setParameter('id',743);
        $q->setMaxResults(1);  // this limits Articles to be only 1
                               // when using $q->getResult();
        $sources=new Paginator($q, $fetchJoin = true);
        $sources=$sources->getIterator();
        var_dump($sources[0]->getArticles());

@adridev Sources is set to Paginator and then discarded by the created paginator->getIterator (the second time sources is overridden is commented out). Thank you for your input though if you have any suggestions on how to make this better please feel free to edit this answer. I have not used Doctrine in a while so I'm not even sure what this answer was about. — HMR, Jan 12 '17 at 07:10

score -2 · Answer 4 · answered Apr 12 '11 at 15:10

-2

Did the same with a querybuilder and it works. Maybe something else is the problem?

$qb->add('select', 'b, c, ch, g')
   ->add('from', 'Broadcast b')
   ->add('groupBy', 'b.title')
   ->add('where', 'b.imageBig != \'\'')
   ->add('orderBy', 'b.starttime ASC')
   ->setMaxResults(10)
   ->leftJoin('b.category', 'c')
   ->leftJoin('b.channel', 'ch')
   ->leftJoin('b.genre', 'g')

answered Apr 12 '11 at 15:10

Christian

1

Thanks, I'll try it with the query builder. Maybe it is just an issue with using DQL. – Stephen Watkins Apr 12 '11 at 15:15
2

This will not work for fetch joined collections. Christian only has fetched join To-One associations. – beberlei Apr 13 '11 at 15:59

Limiting a doctrine query with a fetch-joined collection?

4 Answers4

Linked