What is the most efficient approach to fetch category tree, products, brands, counts by subcategory
- by alex227
Symfony 1.4 + Doctrine 1.2.
What is the best way to minimize the number of queries to retrieve products, subcategories of current category, product counts by subcategory and brand for the result set of the query below? Categories are a nested set.
Here is my query:
$q = Doctrine_Query::create()
->select('c.*, p.product,p.price, b.brand')
->from('Category c')
->leftJoin('c.Product p')
->leftJoin('p.Brand b')
->where ('c.root_id = ?', $this->category->getRootId())
->andWhere('c.lft >= ?', $this->category->getLft())
->andWhere('c.rgt <= ?', $this->category->getRgt())
->setHydrationMode(Doctrine_Core::HYDRATE_ARRAY);
$treeObject = Doctrine::getTable('Category')->getTree();
$treeObject->setBaseQuery($q);
$this->treeObject = $treeObject;
$treeObject->resetBaseQuery();
$this->products = $q->execute();
