annotate() usage in recursion

[plopidou]

Hi,

Imagine the following models:

class Person(models.Model):
    parent = models.ForeignKey('self', null=True, blank=True, on_delete=models.SET_NULL)

    from django_cte import CTEManager
    objects = CTEManager()

class Event(models.Model):
    when = modelsDateTimeField(null=False, blank=False)
    person = models.ForeignKey(Person, null=False, blank=False, on_delete=models.CASCADE, default=None)

Now I would like to get the count() of all the events for a tree/family of Person objects. I am not sure I am doing correctly, but if I am, something is not working? (SubqueryCount is local and confirmed to work)

def make_person_cte(cte):
    return Person.objects.filter(
        parent__isnull=True
    ).annotate(
        event_count=SubqueryCount(
            Event.objects.only('pk').filter(
                person__pk=OuterRef('pk'),
            ),
            output_field=models.IntegerField(),
        ),
    ).union(
        # recursive union: get children
        cte.join(
            Person,
            parent=cte.col.pk
        ),
        all=True,
    )

persons_cte = With.recursive(make_person_cte)
persons = persons_cte.join(
    Person,
    pk=persons_cte.col.pk
).with_cte(
    persons_cte 
)

and the trace looks like the following:

Traceback (most recent call last):
  File "/home/foo/main/pro/web/foo/manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/foo/main/env/lib/python3.9/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/home/foo/main/env/lib/python3.9/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/foo/main/env/lib/python3.9/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/foo/main/env/lib/python3.9/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/home/foo/main/pro/web/foo/utils/management/commands/utils_django_cte.py", line 43, in handle
    campaigns_cte = With.recursive(make_campaigns_cte)
  File "/home/foo/main/env/lib/python3.9/site-packages/django_cte/cte.py", line 53, in recursive
    cte.query = make_cte_queryset(cte).query
  File "/home/foo/main/pro/web/foo/utils/management/commands/utils_django_cte.py", line 25, in make_campaigns_cte
    return Campaign.objects.filter(
  File "/home/foo/main/env/lib/python3.9/site-packages/django/db/models/query.py", line 1091, in annotate
    return self._annotate(args, kwargs, select=True)
  File "/home/foo/main/env/lib/python3.9/site-packages/django/db/models/query.py", line 1130, in _annotate
    clone.query.add_annotation(
  File "/home/foo/main/env/lib/python3.9/site-packages/django_cte/query.py", line 49, in add_annotation
    super(CTEQuery, self).add_annotation(annotation, *args, **kw)
  File "/home/foo/main/env/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1062, in add_annotation
    annotation = annotation.resolve_expression(self, allow_joins=True, reuse=None,
  File "/home/foo/main/env/lib/python3.9/site-packages/django_cte/expressions.py", line 45, in resolve_expression
    for cte in get_query(clone)._with_ctes:
AttributeError: 'Query' object has no attribute '_with_ctes'

Any idea?

thanks!

[millerdev]

This looks like an issue that may be fixed by https://github.com/dimagi/django-cte/pull/46. Your example may be a good starting point to create a test, which is needed before that PR can be merged.

[plopidou]

Thank you.

Just to clarify, this is a dumbed down example of what I am trying to obtain:

person table:

id | parent
1  | NULL
2  | 1
3  | 1
4  | 2
5  | 2
6  | 3

Where:

• 1 is the grandparent ("depth 0")
• 2 and 3 are the parents ("depth 1")
• 4, 5 and 6 are the children ("depth 2")

event table:

id | person
1  | 1
2  | 2
3  | 3
4  | 4
5  | 5
6  | 6

Ideally, I would get:

[
    {id:1, event_count: 6},
    {id:2, event_count: 3},
    {id:3, event_count: 2},
    {id:4, event_count: 1},
    {id:5, event_count: 1},
    {id:6, event_count: 1},
]

... which would be the aggregated count() of all events of self, and all children, for each Person object.

[millerdev]

Fixed by https://github.com/dimagi/django-cte/pull/60

[plopidou]

Great, many thanks to all who contributed! :)

Will this materialise in a 1.2.1 or something?

[ZipBrandon]

@plopidou Did you get this working where you can recursively roll-up the values to the grandparent? I have only figured out how to accumulate the values to the leaves/children.