Django/MSSQL: Issues counting objects through foreignkey relations

Question

environment:

• Database: MSSQL
• Django 3.2.20
• mssql-django 1.3

models

class ChangeOrder(models.Model):
    # ...fields...

class GroupChange(models.Model):
    order = models.ForeignKey(
        ChangeOrder, related_name='groupchanges'
    )
    action = models.CharField(max_length=10, choices=['assignment', 'removal'])
    # ...other fields...

class UserChange(models.Model):
    order = models.ForeignKey(
        ChangeOrder, related_name='userchanges'
    )
    action = models.CharField(max_length=10, choices=['assignment', 'removal'])
    # ...other fields...

objective

For each ChangeOrder, I want to annotate/calculate:

• ant_assignment_count: Count of 'assignment' actions in both GroupChange and UserChange.
• ant_removal_count: Count of 'removal' actions in both GroupChange and UserChange.

query

ChangeOrder.objects.annotate(
    ant_assignment_count=Sum(
       Case(
           When(userchanges__action='assignment', then=1),
           When(groupchanges__action='assignment', then=1),
           default=0, output_field=IntegerField()
       )
   ),
   ant_removal_count=Sum(
       Case(
           When(userchanges__action='removal', then=1),
           When(groupchanges__action='removal', then=1),
           default=0, output_field=IntegerField()
       )
   )
)

objects

co = ChangeOrder.objects.create()
GroupChange.objects.create(order=co, action='removal', ..)
GroupChange.objects.create(order=co, action='removal', ..)
UserChange.objects.create(order=co, action='assignment', ..)

If I run the query with those created objects I receive ant_assignment_count=2 and ant_removal_count=2.
But it should be ant_assignment_count=1 and ant_removal_count=2.

I've attempted various methods including annotations, subqueries, and Count with Case statements. However, I'm encountering issues and getting incorrect results. It seems to be a problem with the LEFT OUTER JOIN on GroupChange and UserChange.

I'd appreciate any help!

Answer 1

I tried differnt approaches and those are the ones I came up with:

Both seem to work for me.

Option #1 (preferred because of simplicity)

ChangeOrder.objects.annotate(
    ant_assignment_count=Count(
        Case(
            When(userchanges__action='assignment', then=F('userchanges')),
            When(groupchanges__action='assignment', then=F('groupchanges')),
        ), distinct=True
    ),
    ant_removal_count=Count(
        Case(
            When(userchanges__action='removal', then=F('userchanges')),
            When(groupchanges__action='removal', then=F('groupchanges')),
        ), distinct=True
    )
)

Option #2 (seems to be more performant)

    assignment_count_qs = UserChange.objects.filter(
        order_id = OuterRef('id')
    ).filter(action='assignment').values('order_id').annotate(count=Count('id', distinct=True))
    
    removal_count_qs = UserChange.objects.filter(
        order_id = OuterRef('id')
    ).filter(action='removal').values('order_id').annotate(count=Count('id', distinct=True))
    
    assignment_grp_count_qs = GroupChange.objects.filter(
        order_id = OuterRef('id')
    ).filter(action='assignment').values('order_id').annotate(count=Count('id', distinct=True))
    
    removal_grp_count_qs = GroupChange.objects.filter(
        order_id = OuterRef('id')
    ).filter(action='removal').values('order_id').annotate(count=Count('id', distinct=True))
    
    ChangeOrder.objects.annotate(
        _ant_direct_assignment_count = Coalesce(Subquery(assignment_count_qs.values('count')[:1], output_field=IntegerField()), 0),
        _ant_direct_removal_count = Coalesce(Subquery(removal_count_qs.values('count')[:1], output_field=IntegerField()), 0),
        _ant_grp_assignment_count = Coalesce(Subquery(assignment_grp_count_qs.values('count')[:1], output_field=IntegerField()), 0),
        _ant_grp_removal_count = Coalesce(Subquery(removal_grp_count_qs.values('count')[:1], output_field=IntegerField()), 0),
        ant_assignment_count = F('_ant_direct_assignment_count') + F('_ant_grp_assignment_count'),
        ant_removal_count = F('_ant_direct_removal_count') + F('_ant_grp_removal_count'),
    )