CODEWITHSUNDEEP
djangodjango-models
Utkucan Bıyıklı
Utkucan Bıyıklı

Reputation: 1117

How can i filter parents that has at least one active child on parent child relationship model

I have "Category" model which has multiple parent/child relationship.

class Category(models.Model):
    pass

class CategoryParent(models.Model):
    parent_category = models.ForeignKey("Category", related_name="children", on_delete=models.CASCADE)
    child_category = models.ForeignKey("Category", related_name="parents", on_delete=models.CASCADE)
    is_active = models.BooleanField(verbose_name=_("Is active?"), default=True)


class CategoryExlusion(models.Model):
    browser = models.ForeignKey("Browser", on_delete=models.CASCADE)
    country = models.ForeignKey("Country", on_delete=models.CASCADE)
    category = models.ForeignKey("Category", on_delete=models.CASCADE)

I want to list all top-level categories that have at least one active child category. If there is a record on the CategoryExlusion list for a category and the requested user's browser and country, that means this category is not active for this user. I can reach the user's browser and country with request.user.browser and request.user.country.

The most difficult issue I'm dealing with is the possibility that my child will have children.

enter image description here

For example, if B, X, D and Y are not active for user's browser and country, A wont be returned by QuerySet even A is active for user's browser and country because there wont be any active child for A.

But, At least one active category (for example x might be active for user's browser and country), A will be returned by QuerySet.

Upvotes: 0

Views: 493

Answers (1)

Araelath
Araelath

Reputation: 595

This might be difficult to achieve with django-orm as you noted, because of the tree-like structure. I think you best bet will be to use Django's Raw SQL queries to perform a Recursive Query. With the recursive query, you can select all descendents (children, and grand-children, and so on ) of a specific top-level category. Then you can filter out all the children that are on the exclusion list, or inactive. If there is at least one remaining descendent, the top level category remains in the queryset.

I haven't tested it, but I think the recursive query for a specific top level category would look something like that:


team = Category.objects.raw('''
    WITH RECURSIVE descendents(id, is_active, child_category, parent_category) AS (
          SELECT id, is_active, child_category, parent_category 
          FROM category
          JOIN category_parent as children on category_parent.parent_category = category.id
          WHERE id = <id of the top level category>
        UNION ALL
          SELECT id, is_active, child_category, parent_category
          FROM descendents 
          JOIN parent_category as children on children.parent_category = descendents.id
        )
    SELECT * FROM descendents
''')

Break down of the query


SELECT id, is_active, child_category, parent_category 
          FROM category
          JOIN category_parent as children on category_parent.parent_category = category.id
          WHERE id = <id of the top level category>

This is the non recursive term, selecting the direct children of the top level category


UNION ALL
          SELECT id, is_active, child_category, parent_category
          FROM descendents 
          JOIN parent_category as children on children.parent_category = descendents.id

This is the recursive term, self-referencing the non recursive term, that will select the next generation of children. This will iterate until there is no children left in the tree.

You could use this recursive query for each top level category using Django's Subquery. However at this point you might be better of doing it in python, performing one recursive query for each top level category. Depending on you database and the numbers of categories, you might hit performance issues due to the back-and-forths with the database

Upvotes: 1

Related Questions