How to select product with multi level categories in sql?

Question

I am using codeigniter and MySQL to build an ecommerce web application. This one required three level of categories. So I have created 3 tables. These are-

category
category_id, category_name

subcategory
subcategory_id,subcategory_name,subcategory_category_id

subsubcategory
subsubcategory_id,subsubcategory_name,subsubcategory_subcategory_id

Here they are linked as parent of one another. Finally I have the product table

product product_id, product_name, product_subsubcategory_id

Now, I need a sql query on this to fetch all product of any specific category.

Something like

$this->Mdl_data->select_products_by_category($category_id);

Please help me on this. I have tried PHP programming to solve this. But it was too slow with lot's of nested loops.

You should look into making the query a stored procedure, which you can build in MySQL then return it the results via php. — Ryan Wilson
@u_mulder I wasn't getting any better way to keep them organized. So I had to. Surely I will go for what's good. Thank you too — BionicTech team

Alex Yokisama Alex Yokisama · Accepted Answer · 2018-03-07T18:36:11

If you need to select all products, that match some specific category, try this request:

SELECT p.product_id, p.product_name, p.subsubcategory_id FROM category c 
JOIN subcategory sc ON sc.subcategory_category_id = c.category_id
JOIN subsubcategory ssc ON ssc.subsubcategory_subcategory_id = sc.subcategory_id
JOIN product p ON p.subsubcategory_id = ssc.subsubcategory_id
WHERE c.category_id = 1;

But you should think about changing your database structure to make your requests faster and simpler.

Edit: Answering the comment about how to improve DB.

Current design of database looks correct, according to actual data relations. 1-many for cat-subcat and 1-many for subcat-subsubcat. But this leads to complicated (and possibly slow) queries while usage.

One way I see is to implement many-many relation with additional restriction. You can create additional table cat-subcat, just as you would do if you needed many-many. But in that table you can set unique limitation to subcat_id, so every subcat could belong only to 1 cat and it becomes in fact 1-many relation. But in this case you can move both up- and downwards the hierarchy. This approach will reduce number of JOINs in your query only by 1, but the whole logic of the query would be easier to understand.

Another way. As I understand this is the query for web-store filter. So, new products will be inserted much more seldom, than viewed by category. You can just add subcat_id and cat_id fields to your product, which is not good idea from the point of data structure, but for this particular situation this might be good solution. Every time new product is inserted to DB, you should control the correctness of those 2 fields by PHP or whatever you use on server. But when products are searched by category you will have simple request without JOINs at all.

Both approaches are based on the idea to sacrifice some space for speeding up and simplifying the queries, that are frequently used. Maybe there is even better solution, but I can't find it right now.

How to select product with multi level categories in sql?

1 Answers