Render a nested set categories with grouped product counts

_list.php

<?php
/**
 * echo the trees as nested ul's with the product counts as title attribute
 */
if (count($trees)) {
  echo _open('ul.category_trees');
  foreach($trees as $tree) {
    if (count($tree)) {
      $html = '';
      foreach($tree as $node) {
        if ($node->level == 0 && $node->itemGrpCnt) {
          $html .= _link($node)->title($node->itemGrpCnt ? __('%count% products',array('%count%' => $node->itemGrpCnt)) : null);
          $html .= _open('ul.category_tree');
        } elseif ($node->itemCnt) {
          $html .= _tag('li', _link($node)->title($node->itemCnt ? __('%count% products',array('%count%' => $node->itemCnt)) : null));
        }
      }
      if ($node->itemGrpCnt > 0) {
        $html .= _close('ul');
      }
      if ($html) {
        echo _tag('li', $html);
      }
    }
  }
  echo _close('ul');
}

components.php

<?php 
...

/**
 * create a custom query with product count and subquery for group count
 * see schema.yml and modules.yml for the Diem configuration
 */
public function executeList() 
{

  $query = Doctrine::getTable('Category')->createQuery('c')
     ->select("c.id, ct.name, COUNT(i.id) as itemCnt, (SELECT COUNT(si.id) FROM Item si LEFT JOIN si.Category sc WHERE si.category_id = c.id OR sc.root_id = c.root_id ) as itemGrpCnt")
    ->leftJoin('c.Translation ct')
    ->leftJoin('c.Items i')
    ->where('ct.lang = ?', $this->getUser()->getCulture())
    ->groupBy('c.id')
  ;

  // fetch tree object and set the query as described in the Doctrine documentation
  $treeObject = Doctrine::getTable('Category')->getTree();
  $treeObject->setBaseQuery($query);
  $rootColumnName = $treeObject->getAttribute('rootColumnName');

  // collect the different trees as described in the Doctrine documentation
  $trees = array();
  $i = 0;
  foreach ($treeObject->fetchRoots() as $root) 
  {
    $trees[$i] = array();
    $options = array(
      'root_id' => $root->$rootColumnName
    );
    foreach($treeObject->fetchTree($options) as $node) 
    {
      $trees[$i][] = $node;
    }
    $i ++;
  }

  // pass to the template
  $this->trees = $trees;
}
...

modules.yml

...

  Catalog:
    categories:
      page: true
      model: Category
      name: Category|Categories
      components:
        list:
        show:

    products:
      page: true
      parent: categories
      model: Item
      name: Product
      components:
        list:
        show:

Render a tree in a Diem list widget.markdown

NestedSet's are a great way of using nested data in a programmatic way. However working with them is quite complex without any helpers. Doctrine provides a NestedSet manager which is built-in into symfony and Diem. While the Diem 5.1-nestedset branch handles nestedsets in the admin, a ready to use nested set is not available in the front widgets.

As this is quiet straightforward and a common feature I'll stow how I usually render a nestedset in a typical category/products relationship.

schema.yml

Category:
  actAs:
    NestedSet:
      hasManyRoots: true
      rootColumnName: root_id
    I18n:
      fields: [ name ]
    DmGallery:
  columns:
    name: string(127)

Item:
  actAs:
    I18n:
      fields: [ name, description ]
    DmGallery:
  columns:
    name: string(127)
    description: clob
    category_id: integer
  relations:
    Category:
      local: category_id
      foreignAlias: Items