John Davidson

php - Laravel/SQL get all parents


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('', $this->main->id)
->when(true, function ($query){
$query->join('forum_categories as parent', 'forum_categories.parent_id', '=', '');

$query->when(true, function($query){
$query->join('forum_categories as sub_parent', 'parent.parent_id', '=', '');


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) {

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

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:

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


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