CODEWITHSUNDEEP
phpsymfonydoctrine-orm
user1074965
user1074965

Reputation: 235

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;
}

Upvotes: 2

Views: 3159

Answers (1)

lheurt
lheurt

Reputation: 411

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.

Upvotes: 1

Related Questions