CODEWITHSUNDEEP
pythondjangodjango-queryset
BlueSapphire
BlueSapphire

Reputation: 311

Keep queryset annotations after combining tow querysets

I'm currently struggling with the behavior of django querysets with annotations and the included operators | and &

I have Models like this:

class Property(models.Model):
    value = models.IntegerField(null=True)

    obj = PropertyManager()

class Profile(models.Model):
    name = models.CharField()
    values = models.ManyToManyField(Property)

class PropertyManager(models.Manager):
    def included(self, profile):
        super(PropertyManager, self).filter(Q(property__profile=profile)).annotate(included_value=Value('true', output_field=CharField()))

    def excluded(self, profile):
        super(PropertyManager, self).filter(~Q(property__profile=profile)).annotate(included_value=Value('false', output_field=CharField()))

    def included_excluded(self, profile):
        return (self.excluded(profile) | self.included(profile)).distinct()

I naively expected that the included_excluded function returns a joined queryset of both querysets with their annotations, like:

property_id | included_value
------------|---------------
          1 |   true
          2 |   false

but it turns out, that the annotation is overwritten in my examples:

j = Profile.objects.get(id=1)
exc = Property.obj.excluded(profile=j)
inc = Property.obj.included(profile=j)
all = Property.obj.included_excluded(profile=j)

len(inc)  => 14
len(exc)  => 17
len(all)  => 31

all.values_list("included_value")  => <QuerySet [('false',)]>
exc.values_list("included_value")  => <QuerySet [('false',)]>
inc.values_list("included_value")  => <QuerySet [('true',)]>

all has obviously not all the correct values in the annotations, as I expected.

So I'm wondering, if there is a method to join two querysets and keep the annotations I made earlier

Upvotes: 1

Views: 296

Answers (1)

wanaryytel
wanaryytel

Reputation: 3492

From Django 1.8 upwards you can do it like this: 

from django.db import models
from django.db.models import Case, When

def included_excluded(self, profile):
    return super(PropertyManager, self).annotate(
        included_value=Case(
            When(property__profile=profile, then='true'),
            default='false', output_field=models.CharField()
        )
    )

If you need them separately, then you can later just filter this queryset: PropertyManager().included_excluded(profile).filter(included_value='true').

Upvotes: 1

Related Questions