John Davidson

php - Laravel/SQL get all parents

0 comments
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.

Rate this post

5 of 5 based on 2617 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us