devoncrazylegs devoncrazylegs - 5 months ago 50
PHP Question

Laravel 5 sub-query for each result row

I am relatively new to Eloquent and Laravel 5 and am using the query builder to create a dynamic query for categories dependant on what variables have been supplied to the function.

In my categories table there is a column called 'parent_id' which indicates whether the category is a sub-category or not. Here is my function so far:

public static function filterCategory($vars) {

$query = Category::query();

if((array_key_exists('order_by', $vars)) && (array_key_exists('order', $vars))) {
$query = $query->orderBy($vars['order_by'], $vars['order']);
}

if(array_key_exists('product', $vars)) {
$query = $query->whereHas('products', function($q) use ($vars){
return $q->where('id', $vars['product']);
});
}

if(array_key_exists('sub_cats', $vars)) {
$query = $query->with('subCategories');
}

return $query->get();
}


I have created a function on the Category model called getChildren which looks like this:

public function subCategories() {
return $this->hasMany('App\Category', 'parent_id', 'id');
}


What I want to be able to do is when my category function is called to return categories, I need a sub-query to be executed for each row which will create a sub-array on the results object containing all categories which have the id of the current category as it's 'parent_id'.
I want this returned:

[
{
"id": "1",
"name": "Dr. Ben Becker DDS",
"description": "Et atque illo ut architecto. Blanditiis laboriosam hic sed quia. Aperiam quis totam distinctio.",
"created_at": "1981-04-07 22:04:40",
"updated_at": "2009-02-26 19:53:09",
"parent_id": null,
"subcategories" : {
//results in here
}
},
{
"id": "2",
"name": "Ms. Winona Lehner",
"description": "Repudiandae nostrum repellendus nulla delectus saepe sapiente ipsam. Delectus voluptatem quis excepturi. Tenetur nostrum et cum quia. Fugiat totam sed dicta totam illo ut dolores.",
"created_at": "2002-01-13 13:42:18",
"updated_at": "1981-10-25 11:39:14",
"parent_id": null,
"subcategories" : {
//results in here
}
},


Now, this would be simple enough for me to do in vanilla PHP and MYSQL but I don't know how this would be implemented in Laravel with the Eloquent querybuilder.

**UPDATE
So I have the subcategories returning but unfortunately not correctly. Here is my output:

[
{
"id": "1",
"name": "Dr. Ben Becker DDS",
"description": "Et atque illo ut architecto. Blanditiis laboriosam hic sed quia. Aperiam quis totam distinctio.",
"created_at": "1981-04-07 22:04:40",
"updated_at": "2009-02-26 19:53:09",
"parent_id": null,
"sub_categories": [
{
"id": "3",
"name": "Dr. Manuel Medhurst",
"description": "Pariatur ut corporis quas sequi dolor totam. Nisi ad amet velit reiciendis voluptates dolore aperiam. Impedit perferendis et a.",
"created_at": "1998-09-20 07:40:23",
"updated_at": "2002-04-01 18:41:24",
"parent_id": "1"
},
{
"id": "4",
"name": "Lee Monahan",
"description": "Nihil qui quod voluptatem non accusamus voluptas. Non et quae velit delectus consequatur vel. Qui totam non rerum sunt nisi soluta sequi. Consequatur eaque quia delectus qui assumenda.",
"created_at": "2014-11-24 06:31:10",
"updated_at": "1996-03-28 18:58:55",
"parent_id": "1"
},
{
"id": "5",
"name": "Miller Osinski",
"description": "Itaque et sed voluptatem earum nulla eligendi tenetur. Et velit ut voluptas similique. Autem rerum sapiente voluptatibus optio esse qui sit et. Alias eligendi aut quibusdam inventore.",
"created_at": "2011-10-08 18:16:56",
"updated_at": "1996-09-29 16:29:37",
"parent_id": "1"
}
]
},
{
"id": "3",
"name": "Dr. Manuel Medhurst",
"description": "Pariatur ut corporis quas sequi dolor totam. Nisi ad amet velit reiciendis voluptates dolore aperiam. Impedit perferendis et a.",
"created_at": "1998-09-20 07:40:23",
"updated_at": "2002-04-01 18:41:24",
"parent_id": "1",
"sub_categories": []
},
{
"id": "4",
"name": "Lee Monahan",
"description": "Nihil qui quod voluptatem non accusamus voluptas. Non et quae velit delectus consequatur vel. Qui totam non rerum sunt nisi soluta sequi. Consequatur eaque quia delectus qui assumenda.",
"created_at": "2014-11-24 06:31:10",
"updated_at": "1996-03-28 18:58:55",
"parent_id": "1",
"sub_categories": []
},
{
"id": "5",
"name": "Miller Osinski",
"description": "Itaque et sed voluptatem earum nulla eligendi tenetur. Et velit ut voluptas similique. Autem rerum sapiente voluptatibus optio esse qui sit et. Alias eligendi aut quibusdam inventore.",
"created_at": "2011-10-08 18:16:56",
"updated_at": "1996-09-29 16:29:37",
"parent_id": "1",
"sub_categories": []
},
{
"id": "2",
"name": "Ms. Winona Lehner",
"description": "Repudiandae nostrum repellendus nulla delectus saepe sapiente ipsam. Delectus voluptatem quis excepturi. Tenetur nostrum et cum quia. Fugiat totam sed dicta totam illo ut dolores.",
"created_at": "2002-01-13 13:42:18",
"updated_at": "1981-10-25 11:39:14",
"parent_id": null,
"sub_categories": []
}
]


At first this looked correct but in my table category 1 only has 1 child (category 4) and category 2 should only 2 children (rows 3 and 5). It would seem that all of these sub-categories are being placed incorrectly into the subcategories array of row 1?

Thanks

Answer

Add a relation in your Category model to itself like this:

public function subcategories()
{
    return $this->hasMany('App\Category', 'parent_id', 'id');
}

Change your Category model namespace if it's not App.

Then you'll be able to query them like this:

$categories = Category::with('subcategories')->get();

If you need you can also group by the parent_id to have them arranged: $categories->groupBy( 'parent_id' )->toJson();

Comments