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();
