Benefits of QueryBuilder vs Lazy Loading in Doctrine 2.0

Question

I am creating a Zend Framework project that utilises Doctrine 2.0

When creating my own custom repository, I originally "cheated" in returning results, instead of working out the DQL (or using QueryBuilder) to obtain results, I just used the model association functions:

$apps = new \Doctrine\Common\Collections\ArrayCollection();
foreach ($reseller->getShops() as $currentShop) {
    foreach ($currentShop->getProducts() as $product) {
        $app = $product->getLicense()->getApplication();
        if (!$apps->contains($app))
            $apps->add($app);
    }
}

I have now spent most of the day learning DQL and how to use QueryBuilder and arrived on the following:

$shopQuery = $this->getQueryBuilder();
$shopQuery->select('s2.id')
    ->from('Software2\DEP\Entities\Shop', 's2')
    ->innerJoin('s.reseller', 'r2')
    ->where($shopQuery->expr()->eq('r2.id', '?1'));

$appQuery = $this->getQueryBuilder();
$appQuery->select(array('a.name AS application_name', 'l.name AS license_name', 's.name AS shop_name'))
    ->from('Software2\DEP\Entities\Application', 'a')
    ->innerJoin('a.licenses', 'l')
    ->innerJoin('l.shops', 'p')
    ->innerJoin('p.shop', 's')
    ->where($appQuery->expr()->in(
            's.id',
            $shopQuery->getDQL()
            )
        )
    ->groupBy('a.name')
    ->orderBy('a.name', 'ASC');
$appQuery->setParameter(1, $reseller->getId());

... which produces the following DQL:

SELECT a.name AS application_name, l.name AS license_name, s.name AS shop_name FROM Software2\DEP\Entities\Application a INNER JOIN a.licenses l INNER JOIN l.shops p INNER JOIN p.shop s WHERE s.id IN(SELECT s2.id FROM Software2\DEP\Entities\Shop s2 INNER JOIN s.reseller r2 WHERE r2.id = ?1) GROUP BY a.name ORDER BY a.name ASC

After all of that effort I find that running the QueryBuilder takes 0.0743ms whereas the original (lazy) method only took 0.0701ms. I can save some time by running the resulting DQL directly (using createQuery), which brings it down to 0.069ms, but is it really worth all that effort??

I assume that the lazy (original) method will result in a lot more queries, or does Doctrine handle that somehow?

Input appreciated

Benefits of QueryBuilder vs Lazy Loading in Doctrine 2.0

0 Answers0