How can I select the IDs of a category and all its (n levels) subcategories?

Question

Beside the parent category, I would like to recursively collect all of its child rows.

(Pseudo code) SELECT IDs FROM #_categories WHERE parent = father

As an alternative, SELECT IDs WHERE the first part of path is the parent's path.

Is this correct? What will be the most efficient way?

Even better:

Is there a standard method (probably in ContentModelCategories) to achieve what I need? Should I use ContentModelCategories::getItems(true)? How do I invoke it, given I already have the "parent" category ID?

A working example will be really appreciated.

Answer 1

Check the mysql join map here:http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg

One of them is what you are looking for.

Answer 2

You could try using a subquery... Given that you know the id of your father category, and the alias is unique, you might want to try this idea (would need to convert it to PHP or SQL code with quotes and so on, of course):

SELECT id 
FROM categories 
WHERE path LIKE CONCAT(%,(SELECT alias FROM categories WHERE id = yourid),%) 
AND WHERE id NOT IN 
  (SELECT id 
   FROM categories 
   WHERE alias LIKE CONCAT(%,(SELECT alias FROM categories WHERE id = yourid))
);

The last part is to prevent the return of father categories of you father category. You might want to get the alias in a separate query to simplify this one and to avoid the second subquery (which is redundant as well). It is too late for me now to think about more efficient solutions, I'll leave that to the others. The result will probably not include the father category, but you already know the id of that one, right.

Not tested.

edit: use the path instead of the alias

SELECT id
FROM categories
WHERE path LIKE CONCAT(
  (SELECT path 
   FROM categories
   WHERE id = yourid),%);

Answer 3

I'm reluctant to publish this as "my answer" as it will probably show how a bad PHP and Joomla programmer I am, but, as it works... here it is:

static function GetCategoriesWithChildren($categories) {
   $results = array();
   $db = JFactory::getDbo();
   foreach ($categories as $baseCategory)
   {
      $query = $db->getQuery(true);
      $query->select('c.path');
      $query->from('#__categories AS c');
      $query->where('c.published > 0');
      $query->where('c.id = ' . $baseCategory);
      $db->setQuery($query);
      $fathersList = $db->loadObjectList();
      foreach ($fathersList as $father)
      {
         $results[] = $baseCategory; // This adds the father only if it is published
         $query = $db->getQuery(true);
         $query->select('c.id');
         $query->from('#__categories AS c');
         $query->where('c.published > 0');
         $query->where('c.path LIKE \'' . $father->path . '/%\'');
         $db->setQuery($query);
         $children = $db->loadObjectList();
         foreach ($children as $category)
         {
            $results[] = $category->id;
         }
      }
   }
   return $results;
}

The function takes an array of categories IDs as its argument and returns an array containing all those IDs and the IDs of any children category.

If anyone could make it better, I will be sincerely grateful and change my "selected answer" to it. Thanks!

Cheers!

smz