environment:
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:
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!
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'),
)