How can I get children categories

Question

Actually I have categories (parent) and subchildrens(childrens) I want to display something like that :

-Parent 1 
-- children 1
-- children 2

-Parent 2
-- children 1
-- children 2

So I thought of making a loop that displays all the parents, then inside a second loop that displays the child categories whose id corresponds to the parent id of the parent category

For the moment I can get all parents categories

A parent looks like this :

And a children like this :

It is possible to get theses children with an SQL request like this or there are others best ways ? :

Select *
From sys_category c
INNER JOIN sys_category c1
ON c.parent = c1.uid

https://learnsql.com/blog/query-parent-child-tree/ this should answer your question. let me know if this works for you. — A H, Sep 09 '22 at 10:26

score 1 · Accepted Answer · answered Sep 09 '22 at 10:32

When you doing INNER JOIN you will get for each child the parent, and this is not a neccesary data, I would do it as Laravel does it.

In more details you have a relation called One-To-Many, each row in your parent's table may have one or more children in another table, so the solution I suggest is to select all parent's, and then select all children by parent id, and then loop it and map to each parent is children.

SELECT * FROM categories;

Gives:

[
    ["uid" => 1, ...],
    ["uid" => 2, ...]
]

Now you are columing the UID field:

$uids = array_column($result_array, 'uid');

Now you can query with those id's:

$sub_children = "SELECT * FROM sys_category where parent IN ("+ implode(",", $uids) +")";

foreach ($parents as $parent) {
    $parent_children = array_filter($sub_children, function ($children) use ($parent) {
        return $children['parent'] == $parent['uid'];
    });
    $parent['children'] = $parent_children;
}

How can I get children categories

1 Answers1