How can I exclude grandchildren from my recursive querying process?

Question

I am trying to limit my db output -- which currently gives me from some categories every child and grandchild -- to only the child. So that no grandchildren are displayed.

I tried to limit the recursion to only happen once, but it doesn't work. I think the answer lies in the db query, but I am not familiar with dbs.

public function getChildCategories(&$result, &$categories, $level = 1)
{
    $db = EB::db();

    $ordering = $this->params->get('order', 'popular');
    $sort = 'desc';
    $total = (int) $this->params->get('count', 0);
    $hideEmptyPost = $this->params->get('hideemptypost', false);
    $language = EB::getCurrentLanguage();

    foreach ($result as $row) {
        // Initialize default structure
        $category = EB::table('Category');
        $category->bind($row);
        $category->cnt = $row->cnt;

        $categories[$row->id] = $category;
        $categories[$row->id]->childs = array();

        // Find child categories
        $query = array();
        $query[] = 'SELECT a.*, COUNT(' . $db->qn('b.id') . ') AS ' . $db->qn('cnt') . ',' . $db->Quote($level) . ' AS ' . $db->qn('level');
        $query[] = 'FROM ' . $db->qn('#__easyblog_category') . ' AS a';
        $query[] = 'LEFT JOIN ' . $db->qn('#__easyblog_post_category') . ' AS pc';
        $query[] = 'ON ' . $db->qn('a.id') . '=' . $db->qn('pc.category_id');
        $query[] = 'LEFT JOIN ' . $db->qn('#__easyblog_post') . ' AS b';
        $query[] = 'ON ' . $db->qn('b.id') . '=' . $db->qn('pc.post_id');
        $query[] = 'AND ' . $db->qn('b.published') . '=' . $db->Quote(EASYBLOG_POST_PUBLISHED);
        $query[] = 'AND ' . $db->qn('b.state') . '=' . $db->Quote(EASYBLOG_POST_NORMAL);
        $query[] = 'WHERE ' . $db->qn('a.published') . '=' . $db->Quote(1);
        $query[] = 'AND ' . $db->qn('parent_id') . '=' . $db->Quote($row->id);

        if ($language) {
            $query[] = 'AND(';
            $query[] = $db->qn('a.language') . '=' . $db->Quote($language);
            $query[] = 'OR';
            $query[] = $db->qn('a.language') . '=' . $db->Quote('');
            $query[] = 'OR';
            $query[] = $db->qn('a.language') . '=' . $db->Quote('*');
            $query[] = ')';
        }

        if (!$hideEmptyPost) {
            $query[] = 'GROUP BY ' . $db->qn('a.id');
        } else {
            $query[] = 'GROUP BY ' . $db->qn('a.id') . ' HAVING (COUNT(' . $db->qn('b.id') . ') > 0)';
        }

        if ($ordering == 'ordering') {
            $query[] = ' ORDER BY `lft` desc';
        }

        if ($ordering == 'popular') {
            $query[] = ' ORDER BY `cnt` desc';
        }

        if ($ordering == 'alphabet') {
            $query[] = ' ORDER BY a.`title` asc';
        }

        if ($ordering == 'latest') {
            $query[] = ' ORDER BY a.`created` desc';
        }

        $query = implode(' ', $query);
        $db->setQuery($query);
        $children = $db->loadObjectList();
        $ccounter = 0;

        // Recursion happens here
        if ($children) {
            $this->getChildCategories($children, $categories[$row->id]->childs, ++$level);
        }
    }
}

Can someone explain to me how to modify the db query? The output now is:

Category
-Child Cat
--Grandchild Cat
-Child Cat

I want:

Category
-Child Cat
-Child Cat

I don't understand. You have built a PHP function with recursion, why do you want to user a limit in MySQL? You could use the $level variable to check your depth in PHP. The best tip i can give is to avoid recursion. If you barely understand your code now, you wont at all when you read in in a year. — Daan, May 07 '19 at 09:58
I didnt build the function, its from the system i am using. I try to modify my site. I thought about the $level variable. If i limit it to 1, it should work. But it doesnt. Maybe i am stucked on that idea — youwhatmate, May 07 '19 at 10:02
That is some wicked-nasty query building going on right there. Is that what is in EasyBlog? Wheeeewwy. You owe it to yourself to get familiar with Joomla's query buidling methods, there is much to clean up. Can you not do something like: `if ($children && $level < 2) { $this->getChildCategories($children, $categories[$row->id]->childs, $level + 1); }`? — mickmackusa, May 08 '19 at 13:53
Thanks for your answer. I will try that. No this doesnt comes directly out of easyblog, it is from the page Build Plugin Quix. — youwhatmate, May 09 '19 at 05:33

score 0 · Accepted Answer · answered Aug 29 '19 at 12:49

0

The comment from the user mickmackusa solves the problem. His solution works perfectly!

if ($children && $level < 2) { 
    $this->getChildCategories($children, $categories[$row->id]->childs, $level + 1); 
}

answered Aug 29 '19 at 12:49

youwhatmate

71
8

How can I exclude grandchildren from my recursive querying process?

1 Answers1