Lets assume that we have to show the category as tree , look like the one in right column of this blog:

To do this, we will create a widget named category in news module.

I don't want to repeat the steps to create a widget, but we should look at the Widget.php and show.phtml.

File Widget.php:

<?php
class Tomato_Modules_News_Widgets_Category_Widget extends Tomato_Core_Widget {
  protected function _prepareShow() {
    $conn = Tomato_Core_Db_Connection::getSlaveConnection();
    
    $sql = 'SELECT node.category_id, node.name, node.slug, (COUNT(parent.name) - 1) AS depth,
          node.left_id, node.right_id
        FROM t_category AS node,
          t_category AS parent
        WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
        GROUP BY node.category_id
        ORDER BY node.left_id';
    $categories = $conn->query($sql)->fetchAll();
    $this->_view->assign('categories', $categories);
  }  
}

- Line 4: Get the database connection.

TomatoCMS support both single database server and multiple master/slave database servers via two APIs:

Tomato_Core_Db_Connection::getMasterConnection();

to connect to master server, and:

Tomato_Core_Db_Connection::getSlaveConnection();

to connect to slave one.

- Line 13: Execute query using methods provided by Zend_Db_Adapter_Abstract class

- Line 14: Assign the array of categories to view layer

File show.phtml:

<div class="t_news_category">
  <h2><?= $this->translates['news_category']->_('title'); ?></h2>
  <?php foreach ($this->categories as $category) : ?>
  <div<?php if ($category->depth > 1) : ?> 
 style="padding-left: <?= ($category->depth - 1) * 20; ?>px"<?php endif; ?>>
      <span<?php if ($category->depth == 0) : ?> 
         style="background: none; padding-left: 0"<?php endif; ?>>
        <a href="/news/category/view/<?= $category->category_id; ?>/">
          <?= $category->name; ?>
        </a>
      </span>
    </div>
  <?php endforeach; ?>
</div>

- Line 3: Loop over the array of categories

- Line 4, 6: Check whether the depth of category is greater than 1 or not, then set the style for div or span container.

At this step, our category tree look beauty:

But our customers do not satisfy with this. They want to show the number of articles belong to category next to the link to category.

So, maybe we have to rewrite the query from Widget.php, to get the number of articles in each category under alias num_articles, for example, and in show.phtml, display the value as follow:

<a href="/news/category/view/<?= $category->category_id; ?>/"><?= $category->name; ?></a> 
(<?= $category->num_articles; ?>

but this can cause a complex query.

Now, it is the time to use view helper in Widget scope.

To do this, you have to create file named Helper.php in the same folder as Widget.php

<?php
class News_Widgets_Category_Helper {
  public function helper() {
    return $this;
  }
  
  public function count($categoryId) {
    $conn = Tomato_Core_Db_Connection::getSlaveConnection();
    $select = $conn->select()
          ->from(array('a' => 't_news_article'), array('num_articles' => 'COUNT(*)'))
          ->where('a.category_id = ?', $categoryId)
          ->where('a.status = ?', 'active')
          ->limit(1);
    $row = $select->query()->fetch();
    return $row->num_articles;
  }
}

<a href="/news/category/view/<?= $category->category_id; ?>/"><?= $category->name; ?></a> 
(<?= $this->helper()->count($category->category_id); ?>

$this->helper()->nameOfMethod(...)