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)?

Rochellerochemont

Answer 1

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.

Kautz

A note here, foreach ($results as $post) { echo $post "\n"; } $post is each row of the data. – Dreary

Answer 2

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.

Shrewmouse

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. – Rochellerochemont

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. – Shrewmouse

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. – Rochellerochemont

Answer 3

If you use KnbPaginator, you just need to

config/packages/knp_paginator.yaml

knp_paginator:
  page_range: 10                     # number of links shown in the pagination menu (e.g: you have 10 pages, a page_range of 3, on the 5th page you'll see links to page 4, 5, 6)
  default_options:
    page_name: page                 # page query parameter name
    sort_field_name: sort           # sort field query parameter name
    sort_direction_name: order      # sort direction query parameter name
    distinct: false  # <<< ADD THIS LINE <<<

OR you can this

$paginator = $this->container->get(PaginatorInterface::class);
$paginator->paginate($query, $page, $limit, ['distinct' => false]);

Add

Answer 4

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());

Roadbed

@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. – Roadbed

Sorry, I didn't see the comment. My fault. – Sugarcoat

Answer 5

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')

Razid

Thanks, I'll try it with the query builder. Maybe it is just an issue with using DQL. – Rochellerochemont

This will not work for fetch joined collections. Christian only has fetched join To-One associations. – Shrewmouse