CODEWITHSUNDEEP
mysqldjangodjango-queryset
HBat
HBat

Reputation: 5692

Getting descendents of objects in Django

I'd like to write a method that returns all descendents (i.e. children, grandchildren, great-grandchildren ...) of an object as a QuerySet.

I wrote two methods, the first one (get_all_children) can return children and grandchildren but gives the following error if there are great-grandchildren (or lower-descendants): 

django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT DISTINCT `deneme_app_person`.`id`, `deneme_app_person`.`name`, `de' at line 1")

The second method (get_all_children_list) works just fine (as far as I tested) but possibly slower and last line does not look good.

models.py

from django.db import models


# Create your models here.
class Person(models.Model):
    name = models.CharField(max_length=100)
    parent = models.ForeignKey(
        'self', null=True, blank=True, related_name='children',
        on_delete=models.CASCADE)

    def get_all_children(self):
        """
        This method will return all children and grandchildren of Person objects
        :return:
        """
        p_list = self.children.all()
        if p_list.count():
            for p in p_list:
                children = p.get_all_children()
                if children.count():
                    p_list = p_list.union(children)
        return p_list.distinct()

    def get_all_children_list(self):
        p_list = self.children.all()
        if p_list.count():
            for p in p_list:
                children = p.get_all_children_list()
                if children.count():
                    p_list = set(p_list).union(children)
                    # p_list = list(set(p_list + children))
        return Person.objects.filter(id__in=[p.id for p in p_list])
        # return list(p_list)

    def __str__(self):
        return f"({self.id}) - {self.name}"

tests.py

from django.test import TestCase
from django.db.models.query import QuerySet

from .models import Person


class PersonTests(TestCase):
    def test_get_all_children(self):
        p = Person.objects.create(name='Jack')
        p1 = Person.objects.create(name='Jack Jr', parent=p)
        p2 = Person.objects.create(name='Jill', parent=p)
        p11 = Person.objects.create(name='Jack Jr2', parent=p1)
        p111 = Person.objects.create(name='Jack Jr3', parent=p11)
        p112 = Person.objects.create(name='Jill Jr', parent=p11)

        # Following test is working
        self.assertEqual([p11, p111, p112], list(p1.get_all_children()))
        # Following test is not working
        self.assertEqual([p1, p2, p11, p111, p112], list(p.get_all_children()))

        for person in [p, p1, p2, p11, p111, p112]:
            self.assertIsInstance(person.get_all_children(), QuerySet)

    def test_get_all_children_list(self):
        p = Person.objects.create(name='Jack')
        p1 = Person.objects.create(name='Jack Jr', parent=p)
        p2 = Person.objects.create(name='Jill', parent=p)
        p11 = Person.objects.create(name='Jack Jr2', parent=p1)
        p111 = Person.objects.create(name='Jack Jr3', parent=p11)
        p112 = Person.objects.create(name='Jill Jr', parent=p11)
        p1111 = Person.objects.create(name='Jack Jr4', parent=p111)

        self.assertEqual([p1111], list(p111.get_all_children_list()))
        self.assertEqual([p111, p112, p1111], list(p11.get_all_children_list()))
        self.assertEqual([p11, p111, p112, p1111], list(p1.get_all_children_list()))
        self.assertEqual([p1, p2, p11, p111, p112, p1111], list(p.get_all_children_list()))

        for person in [p, p1, p2, p11, p111, p112, p1111]:
            self.assertIsInstance(person.get_all_children_list(), QuerySet)

Can you point out what is wrong with the first method? How to correct that method?

Upvotes: 1

Views: 588

Answers (1)

Rieljun Liguid
Rieljun Liguid

Reputation: 1531

I have reproduced your error locally but I haven't figured out the reason behind it yet. If you don't mind not using union, you can use | operator to merge two querysets, so your method will become like this:

def get_all_children(self):
    children = self.children.all()
    for child in children:
        children = children | child.get_all_children()

    return children

Upvotes: 1

Related Questions