Hello everyone. I'm almost new to laravel and have difficulties using eloquent models and querying them when it gets a little complicated. I have a table of products and each product belongs to a category from categories table. The model is HasMany and BelongsTo, and I have no problems with selecting a product and its categories' information and vice versa. Categories table itself has a HasMany and BelongsTo relationship to itself through a column named 'parent_id'. and there is another column named 'has_product' which is a boolean, When true, it means there will be no more categories as children for this category. Now, I have a route say 'shop/{id}', in which 'id' is from categories. It could be any of category records. What I need first, is an ordered list of all parent nodes of this category. Secondly, I need to select every product that can be sub-product of 'id'. If not directly, through a few children of 'id'.
Example:
categories: id name parent_id has_product #1 clothes NULL 0 #2 men 1 0 #3 women 1 0 #4 high heels 3 1
products: id name category_id #1 example 4
Now in 'shop/ 1' I want something like 'clothes/women/high heels' and I want product #1 to be selected.
Sounds to me like you have a pretty bad database model. You are talking about recursive queries, and those are always tricky.
You have products and categories right? Do you have sub categories as well?
To keep it simple you should probably split the categories table into categories and sub categories.
Can the product belong to more than one category or sub category?
If it's only one category then add category_id to your products table, if it is more than one you need to use a bivot table called category_product. The same goes for sub categories, the sub category has a category_id to make it belong to a category, then you use a either a sub_category_id on the product if it only can have one sub category, if nopt you use a pivot between the sub categories and the products called product_sub_category.
I suggest you give this post a read.
Thanks, I already have that relation between categories and products! Here is table structure for both/ $table->id(); $table->string('title'); $table->unsignedBigInteger('category_id'); $table->foreign('category_id')->references('id')->on('categories');
$table->id();
$table->string('name');
$table->string('slug')->unique()->nullable();
$table->unsignedBigInteger('parent_id')->nullable();
$table->foreign('parent_id')->references('id')->on('categories')->onDelete('cascade');
And I cannot separate categories, because there could be different number of levels of category. It IS possible, but it won't help me.
@saba_mdn Then you need to start looking at recursive queries. I'm guessing you are using MySQL.
https://www.mysqltutorial.org/mysql-basics/mysql-recursive-cte/
sometimes its easier to split the queries, so first option would be a function that gets all child categories. your problem will be if you have an unknown number of levels of descendants. if you put a limit on it, at say 3, you could do
$cat_ids = Category::with('children:id.children:id.children:id)->find($cat);
then flatten this into an array of ids which represent all the category ids of children of the chosen category
you can then query the products
$products = Product::with('category')->whereIn('category_id',$cat_ids)->get();
Please or to participate in this conversation.