Answer the question
In order to leave comments, you need to log in
How to get categories if their descendants have a product?
The actual structure
Категории
ID Название Родитель
1 Категория1 null
2 Категория2 1
Товары
ID Название Категория
1 Товар 1 2
Answer the question
In order to leave comments, you need to log in
First, for tree structures, always use MPTT . Otherwise, tree traversal will be long, resource-intensive and dependent on the number of elements and their nesting level.
Secondly, ORM will not cope with this task. And on pure SQL it is possible.
Suppose we have a "catalog" application and the models in it are:
class Category(MPTTModel):
parent = TreeForeignKey('self', verbose_name=u'Родитель', null=True, blank=True, related_name='children')
name = models.CharField(u'Название', max_length=100)
class Product(models.Model):
category = TreeForeignKey(Category, verbose_name=u'Категория')
...
SELECT name FROM catalog_category AS cc
INNER JOIN LATERAL (
SELECT cc.id AS id, SUM(products) AS p_count FROM (
SELECT cc.id AS parent_id, category_id, COUNT(id) AS products
FROM catalog_product
WHERE category_id IN (
SELECT id FROM catalog_category
WHERE lft <= cc.rght AND lft >= cc.lft AND tree_id = cc.tree_id)
GROUP BY category_id
) AS sub_cс
GROUP BY parent_id
) AS cp
USING(id) WHERE cp.p_count > 0;
One option is to take a product model, group them into categories = a list of categories.
With parents, this is already building trees. Above you get all the categories that have products, then you look to see if there is a parent, and if there is, you climb up the tree (if the structure is not initially tree-like, and the depth of the categories is more than 2x).
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question