Question

Following the instructions of this article i want to get a full tree with each category s depth which works just fine.

我还希望获得每一类产品。我试图增加另一个UNT(产品——类别.产品_id),但深值。我如何改变每个类别产品数量和深度的询问?

products_categories
----------------------
category_id
product_id


SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
    nested_category AS parent
 WHERE node.lft BETWEEN parent.lft AND parent.rgt
 GROUP BY node.name
 ORDER BY node.lft

In other words I want to combine these two queries.

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
    nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft

SELECT parent.name, COUNT(product.name)
FROM nested_category AS node ,
    nested_category AS parent,
    product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;

Answer 1

我最后解决了我的问题。我将上述问题合并在一起,以便我能够深入了解情况,把产品计算成象。

SELECT parent.id,
                   parent.lft,
                   parent.rgt,
                   (SELECT COUNT(parent2.id) FROM businesscategories AS parent2 WHERE parent.lft > parent2.lft AND parent.rgt < parent2.rgt) AS depth,
                   COUNT(b_c.business_id) AS bcount

                FROM businesscategories AS node,
                businesscategories AS parent,
                businesses_categories AS b_c,
                WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id = b_c.category_id
                GROUP BY parent.id having depth > 0
                ORDER BY parent.lft

友情链接