John Davidson

Message:

I have some slight problems with MySQL.
I have a categories table, where I have parent_id and it's associated with the same table (foreign id). So I'm trying to get all parents (not child), that are associated with all the other.

For example, I have 3 rows in the table

id     title     parent_id

1      test      null

2      test1     1

3      test2     2

So, when I want to get all parents for id = 3, I would need an response like (including the main one at the end):

[

 {

   id: 1

   title: test

 },

{

   id: 2

   title: test1

 },

{

   id: 3

   title: test2

 }

]

I tried like:

$parent = Category::where('forum_categories.id', $this->main->id)

                ->when(true, function ($query){

                    $query->join('forum_categories as parent', 'forum_categories.parent_id', '=', 'parent.id');

                    

                    $query->when(true, function($query){

                        $query->join('forum_categories as sub_parent', 'parent.parent_id', '=', 'sub_parent.id');



                    });

                })

                ->first();

but it's only getting a first parent and others are not shown.
And I have laravel relation for parent and looping a parents, and it works. But I need to get only certain parameters like title and it's not working. Any ideas how to select here or to continue with join?

$parent = Category::where('id', $this->main->id)

                ->with(['loopParents' => function ($query) {

                }])

                ->first();

---

My Answer:
>id)
->join('forum_categories as parent', 'parent.id', '=', 'forum_categories.parent_id')
->select('parent.id', 'parent.title')
->get();

But this query only returns the immediate parent of the given id. To get all parents, you can use a recursive query or a loop to fetch all parents until you reach the top-level parent (where parent_id is null).

Here is an example using a recursive function to get all parents:

php
function getAllParents($categoryId) {
$category = Category::find($categoryId);
$parents = [];

while ($category->parent_id) {
$category = Category::find($category->parent_id);
$parents[] = $category;
}

return array_reverse($parents);
}

$parents = getAllParents($this->id);


This function will return an array of all parent categories for the given id. You can then use this array as needed in your application.