Recursive quires is frequently needed when you have a self referencing table
Our example today will be items and categories user wants to get item details with item category path to root category.
Root-Category > Category > Sub-Category > leaf-Category
#models.py
class Category(models.Model):
name = models.CharField(max_length=127)
parent = models.ForeignKey('self', null=True)
We need to implement query that get the category path to root:
The naive solution:
def path_to_root_category(category_id):
if category_id is None:
return []
category = Category.objects.get(pk=category_id)
result = path_to_root_category(category.parent_id)
result.append(category)
return result
path_to_root_category(4)
[<Category: root>, <Category: category>, <Category: sub-category>, <Category: leaf-category>]
The cost of this function is 4 queries (number of nodes in the path)
If we have a path of large number of nodes this will be so bad.
The optimized solution:
def path_to_root_category(category_id):
query = '''
WITH RECURSIVE parents AS (
SELECT category.*, 0 AS relative_depth
FROM category
WHERE id = %s
UNION ALL
SELECT category.*, parents.relative_depth - 1
FROM category,parents
WHERE category.id = parents.parent_id
)
SELECT id, name, parent_id, relative_depth
FROM parents
ORDER BY relative_depth;
'''
return Category.objects.raw(query, [category_id])
First find the target category element and make it the first element of the
parents
setFollow parent of all categories in
parents
set and add them to the same setFinally select all categories in the
parents
set ordered by their depth
Top comments (0)