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.

  • You should look into JTableNested and then google nested sets. The left and right IDs are really what you should be using for this. – Mathew Lenning Jun 6 '14 at 4:58
  • Sorry I can't give an answer, but the concept is still a little fuzzy to me as well. – Mathew Lenning Jun 6 '14 at 4:59
  • Thank you @MathewLenning! I'm currently out of town, but I'll follow your hints asap. Cheers, smz! – smz Jun 6 '14 at 22:44
  • Awesome. If you figure it out will you post it here? I'd be very interested in the solution as well. – Mathew Lenning Jun 10 '14 at 5:14

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.

  • Thank-you @user1507! You pointed me to a very nice and useful "cheat sheet", but as an answer to my original question, you will forgive me, but it reminds me the Gospel of Matthew: Matthew 7:7-8, to be more precise (en.wikipedia.org/wiki/Matthew_7:7). I up-pointed your answer, anyway, as it is useful! – smz Jun 9 '14 at 23:21

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):

FROM categories 
WHERE path LIKE CONCAT(%,(SELECT alias FROM categories WHERE id = yourid),%) 
  (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

FROM categories
  (SELECT path 
   FROM categories
   WHERE id = yourid),%);
  • 1
    sorry, but I'm afraid I can't accept this as a valid answer for the following reasons: 1) in case I'll select my father's id PATH (not alias) an check it against other categories path 2) although not necessary it is absolutely OK for me to get my father's id (yourid...), but in case I didn't want it, I think a simple "AND WHERE id != yourid" clause would suffice instead of the convoluted "NOT IN (SELECT..." clause that, if I'm not mistaken, just returns... yourid! – smz Jun 1 '14 at 13:43
  • Well... if you work with the path instead of the alias, you don't need the second part, that's true :-) the NOT IN excludes the parent categories of your parent category, if there are any (%alias), as the first part collects all categories where path contains alias (%alias%). I'll edit the answer to do it with the path. – elk Jun 1 '14 at 15:00

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->from('#__categories AS c');
      $query->where('c.published > 0');
      $query->where('c.id = ' . $baseCategory);
      $fathersList = $db->loadObjectList();
      foreach ($fathersList as $father)
         $results[] = $baseCategory; // This adds the father only if it is published
         $query = $db->getQuery(true);
         $query->from('#__categories AS c');
         $query->where('c.published > 0');
         $query->where('c.path LIKE \'' . $father->path . '/%\'');
         $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!



  • I have several refinements that I would like to offer -- not only to the benefit of you and your project(s) but for the benefit of future researchers as well. I generally like to post solutions that I can prove to be correct. If you would please post an sqlfiddle link which holds a sufficient amount of export data and your desired resultset, then I will supply a comprehensive answer. – mickmackusa May 28 '18 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.