Symfony2 efficient displaying list of categories and related products

I've got object Category and Product relation (one-to-many). I want to display list of all categories and objects related to them. If I use:

$categories = $this->getDoctrine()->getRepository('AcmeZebraBundle:Category')->findAll();

And than display this like that:

<ul>
{% for category in categories %}
    <li>{{ category.name }}</li>
    <ul>
    {% for product in category.products %}
    <li>{{ product.name }}</li>
    {% endfor %}
    </ul>
{% endfor %}
</ul>

It will generate additional query for each category. I tried to add products to categories - instead of findAll() I used a method which retrieves all objects and add them to ArrayCollection of an appropriate category. But this does not reduce number of queries.

public function findAllLoadProducts()
{
    $categories = $this->findAll();
    $categortiesById = array();

    foreach ($categories  as $category)
    {
        $categortiesById[$category->getId()] = $category;
    }

    $products = $this->getEntityManager()->getRepository('AcmeZebraBundle:Product')->findAll();

    foreach ($products as $product)
    {
        $categortiesById[$product->getCategory()->getId()]->getProducts()->add($product);
    }

    return $categortiesById;
}

Answers


You need to hydrate the products to the categories in a custom query. Create a Repository for your Category entity in the same bundle (or use it if you already created it):

<?php
/* src/Acme/ZebraBundle/Repository/CategoryRepository.php */
namespace Acme\ZebraBundle\Repository;

use Doctrine\ORM\EntityRepository;

class CategoryRepository extends EntityRepository
{
    public function retrieveHydratedCategories()
    {
        return $this->createQueryBuilder('c')
                    ->select('c, p')
                    ->leftJoin('c.products', 'p')
                    ->getQuery()
                    ->execute();
    }
}

You can then use this query in place of your previous "findall":

$categories = $this->getDoctrine()->getRepository('AcmeZebraBundle:Category')->retrieveHydratedCategories();

The leftJoin makes the custom query fetch the products in the same query, avoiding any further query when you iterate on them in your template.


Need Your Help

NServiceBus default “stop” behavior

.net nservicebus behavior

Say my subscriber is processing a message in a handler and takes about 1 min or more to complete.

iPhone app to iPad app

iphone ios xcode ipad interface-builder

I had created an iPhone app long time back. Now I want to kind of convert the same into an iPad app (the code would more or less remain the same...I only want to redesign the xib for iPad size)