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
4 of 5 based on 5797 votesComments