如何获取某个类别及其子类别(以及子子类别)中所有产品的数量?
类别表如下所示:
id -- name -- parent_id
1 -- Men -- 0
2 -- Women -- 0
3 -- Shirts -- 1
4 -- Half-sleeve -- 3
5 -- Full-sleeve -- 3
关系表:
Product_id -- Category Id
1 -- 2
2 -- 2
3 -- 4 ....
我可以轻松轻松地检索任何一个类别及其直接子类别中的产品数量。但如果超过 2 个级别,事情就会变得混乱。
所以我的问题是如何获取男士及其子类别中所有产品的数量。或者衬衫及其子类别?
任何想法,谢谢。
更新:
我知道有嵌套集模型,但我现在无法将结构更改为该模型。
The category table looks like somewhat as below:
id -- name -- parent_id
1 -- Men -- 0
2 -- Women -- 0
3 -- Shirts -- 1
4 -- Half-sleeve -- 3
5 -- Full-sleeve -- 3
Relationship table:
Product_id -- Category Id
1 -- 2
2 -- 2
3 -- 4 ....
I can retrieve the number of products in any one category and its immediate sub categories with ease with ease. But if there are more than 2 levels things get messy.
So my question is How do I get the number of all of the products in Men and its sub categories. Or Shirts and its subcategories?
Any ideas, Thanks.
UPDATE:
I know there is Nested Set Model but I am not in position to change the structure to that now.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
如果可能的话,我会查看 管理 MySQL 中的分层数据 。
一开始很难让你头脑清醒,但它会让这样的任务变得更容易。
如果你不能做到这一点,你将不得不执行递归函数,例如:
If it is possible I would check out Managing Hierarchical Data in MySQL.
It is hard to get your head around at first, but it makes tasks like this much easier.
If you can't do this, you will have to do a recursive function, e.g.:
假设您可以向类别表添加额外的列。
所述列将具有类别的路径。
这样,查找所有子类别就变成了一个查询:
并且获取类别及其子类别中所有产品的计数也非常容易:
非常高效的查询。
本文提供了更多信息:更多树和树SQL 中的层次结构
Assuming you can add an extra column to the categories table.
Said column will have the path to the category.
That way finding all the subcategories becomes one query:
And to get the count of all the products within a category and its childrens is pretty easy too:
Pretty efficient query.
This article provides more information: More Trees & Hierarchies in SQL