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

Sharepoint Foundation versus Server - Workflows

sharepoint sharepoint-2010 workflow sharepoint-workflow

I have been doing research on Sharepoint 2010 Foundation vs Server in relation to Workflow. Here is what I've researched/setup:

jQuery dialog dynamic height

javascript jquery html jquery-dialog

I am looking for a way to make jQuery UI Dialog height dynamically change according to the contents.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.