K
K
Kamo Petrosyan2016-11-07 03:10:12
Django
Kamo Petrosyan, 2016-11-07 03:10:12

How to get categories if their descendants have a product?

The actual structure

Категории
ID   Название       Родитель
1    Категория1    null
2    Категория2    1

Товары
ID     Название     Категория
1       Товар 1        2

Accordingly, you need to get all the categories if they and/or their descendants contain a product.
The hint will work for both Django ORM and pure SQL.
Thank you!

Answer the question

In order to leave comments, you need to log in

2 answer(s)
S
Sergey Gornostaev, 2016-11-07
@Haikson

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'Категория')
    ...

Then the SQL query will be like this:
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;

Naturally, it will work on adult DBMS with support for modern SQL standards. For example, on PostgreSQL.

A
Alexander, 2016-11-07
@syschel

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 question

Ask a Question

731 491 924 answers to any question