CODEWITHSUNDEEP
doctrineleft-joinlimit
Siol
Siol

Reputation: 411

Doctrine Left join with limit

I made this request for get all brands and all items of theses brand with leftJoin :

$brands = Doctrine_Query::create()
->from('Brand b')
->leftJoin('b.Item i')
->fetchArray();

But I want to get only 10 items of each brand, how can I put the limit on Item leftJoin ?

Upvotes: 5

Views: 2829

Answers (2)

HMR
HMR

Reputation: 39290

I know it's late but this actually scored nr 1 on my google search and is unanswered: Limiting a doctrine query with a fetch-joined collection? suggests using Paginaror object

Here is an example:

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.

        // 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();
//      $sources=$q->getResult();
        var_dump($sources[0]->getArticles());

Upvotes: 1

Zeljko
Zeljko

Reputation: 5158

You have to use subquery like this:

->leftJoin('b.Item i WITH i.id IN (SELECT i2.id FROM Item i2 WHERE i2.brand_id=b.id LIMIT 10)')

I didn't test this but it should work.

Upvotes: 1

Related Questions