Today I Am Gonna Show You How To Get All Subcategory Ids Of A Parent Category Both “Optimized & Unoptimized” .
“Performance & Optimization is something that I always aim for , whether it’s by Query Optimization or Code Refactoring .”
Today we will look on query optimization on how to reduce your queries when dealing with recursive functions or getting the subcategory ids list .
I know its difficult to understand Recursive functions and its even more difficult to create one .
So i will try to explain it much clear if i can .
Requires: Knowledge Of How Recursion Works, Strong OOPS Concepts, Laravel, PHP, Strong DB Design & RDBMS Knowledge.
Ex:- A Basic Category Tree Structure look like this
Now lets take a look at how category structure can look like, we are storing parent id which refers to the main category id .
I hope you know how this structure works if not there are several articles and videos explaining db structure of categories and subcategories .
Category DB Design With Products.
Now you might have seen in many websites the further you click on the subcategories the results get much shorter and unique .
That means if you select the parent category it will show you all the products relying on that category, and as you go much deeper it will show you more products related to that category .
eg;- If you select Electronics category it will show you all the products of Electronics subcategories like gaming,laptops,pc’s etc…
so how do we do that ? how we are gonna fetch products of those subcategories ?
Well if you understand the pattern of the db strucutre of categories you can see that this design is not a loop based, but a nested based so in a nested relationship or design we use recursive approach .
Now you might have heard this term but you might not know how it is used in real life scenario .
Recursive functions are mostly used when you have nested design pattern or structure .
Some Examples:
1st Example: A folder can have many folder and each folder can have more folders
2nd Example: A comment can have many replies and each replies can have its own replies
3rd Example: A category can have many subcategories and each subcategory can have multiple subcategories
I have given you some examples of real world scenarios now today we are gonna look how to code a recursive function to get products of subcategories .
Imp: To get this or in order to make this work we have to get all the subcategories ids of the main category in the array after that we can put that array in the products query like this :
Product::orWhereIn('id',$subCategoryIds)->get();
Which will fetch all the products of that categories which are given in the array .
Let’s take a closer look at how it works !
First we will create a Recursive function called “getSubcategoryids” and
we will add 1 parameter called $categoryId (Main Category Id)
We will create an array called “children” inside the function where we will store all the subcategory ids of the parent and in the second step we will store the parameter id which will be different every time when the function will be called so we will append that category id in every run of function
public function getSubcategoryids($categoryId)
{
$children = [];
$children[] = $categoryId
}
Then we will make a query to find the subcategories via its parent id
It will look like this :
$parentCategories = Category::where('parent_id', $categoryId)
->latest()
->select('id')
->get()
This query will get all the subcategories which have parent_id of the main category .
public function getSubcategoryids($categoryId)
{
$children = [];
$children[] = $categoryId
$parentCategories = Category::where('parent_id', $categoryId)
->latest()
->select('id')
->get()
foreach ($parentCategories as $category)
{
$children[] = getSubcategoryids($category->id,$children);
}
return Arr::flatten($children);
}
After that we will run loop for each parent categories and we will run the function again inside the loop and will pass the category id parameter which be the subcategory id and will store the function values in the children array and return it .
as the results will be nested we have to flatten the array so we have use Arr::flatten here .
public function getSubcategoryids($categoryId)
{
$children = [];
$children[] = $categoryId
$parentCategories = Category::where('parent_id', $categoryId)
->latest()
->select('id')
->get()
foreach ($parentCategories as $category)
{
$children[] = $this->getSubcategoryids($category->id,$children);
}
return Arr::flatten($children);
}
public function index()
{
$category = Category::where('parent_id',4)->first();
$subcategoryIds = $this->getSubcategoryids($category->id)
dd($subcategoryIds);
//results: 4,5,8,12,3,7,8
}
Now we call the function again and will pass the parameter of the category id which we want the subcategories of .
we will store the recursive function results in the variable
and if you dd() the results you can see you will get all the subcategory ids .
But now the problem is if you see the debug bar on every call of the function the query is running which can lead to performance issues if you have several level of categories .
Instead we have an Optimized Solution instead of running query on each call of the function we can filter out the categories who have parent id of the main category didn’t got it ? Let’s dig deeper!
We can create a function where we will pass 3 parameters
** 1st parameter:** it will have the all the categories list
2nd parameter : Children array where you will store all the Subcategory Ids
3rd parameter: Parent id that will help you to find the Subcategories of .
1st Step:
We will take out categories list and we will check if the the categories list parent id is equal to the given parent id which we want to find the subcategories of , if yes then we will filter out those categories and store it into a variable called “subcategories” .
2nd Step:
We will loop through each subcategories and we will append those subcategory ids in the children array .
3rd Step:
We will call the function again and will add the subcategory id to the parameter so that we can find its subcategories too and the id will be served as the parent id in the next function call, and this process will keep on repeating until the parent id is not equal to the given parent id .
public function getSubcategoryids($categories, &$children, $parent_id = null)
{
//filter out the categories who have subcategories by using its parent id
//return those categories if the category parent id
//matches our parent id of the parameter
$subcategories = $categories->filter(function ($item) use ($parent_id)
return $item->parent_id == $parent_id;
});
//loop through each subcategories
foreach ($subcategories as $subcategory)
{
$children[] = $subcategory->id; //append each subcategory in the array
//call the function again and send the subcategory id as a parameter
//to check if it has subcategories or not
$this->getSubcategoryids($categories, $children, $cat->id);
}
}
public function index()
{
//your category list where the you will filter the subcategory
// by its parent id in the function
$categories = DB::table('categories')->select('id', 'parent_id', 'name')->get();
$subCategoryIds = []; //the array where we will store all the subcategory
//we call the function to store the results in the array
//and passing the category id to find the subcategories
getSubcategoryids($categories, $subCategoryIds,$categoryId);
}
Now you can see only single query is ran which is really good if you have multiple level of categories
and you can add these subcategory ids into the product query like this:
Product::orWhereIn('id',$subCategoryIds)->get();
and you will see it has now fetched all the products of categories and subcategories .
So, I hope you enjoyed this article, if you are interested in Code Refactoring, Code & Query optimization don’t forget to follow me
as you will get alot of articles related to this .
Next Article: Shifting Additional Logics Into Traits.
Top comments (1)
I don't know how to feel about this, you left some minor typos and mistakes in code and formatting is driving me crazy.
I see your intentions were good but a little bit poor execution.
As a simple recursion example it is good but I needed to read the article twice to understand. Maybe add more examples of execution? Or show filtering by doing foreach, cat or category in code?
Good article, but fix a bit please.