Allow different filters on multiple aggregates

[Kyle Agronick]

This is the query I that appears to be impossible to implement using the ORM:

SELECT id, name, start_date, 
(SELECT count(state) FROM jobs_job WHERE  state = 'F' and job_group_id  = jg.id GROUP BY state) as badcount , 
(SELECT count(state) FROM jobs_job WHERE state = 'C'  and job_group_id  = jg.id GROUP BY state) as goodcount 
FROM jobs_jobgroup as jg

The main table represents a job group. The two queries tell how many successes and failures there are in the jobs for the job group. Currently, there is no way to have multiple aggregates with different filters on each one. You can't do annotate(fail_count=Count('job__state').filter(state='F'), good_count=Count('job__state').filter(state='C')) or something similar.

[Simon Charette]

Does the following ​conditional aggregation works for you?

from django.db.models import Case, When

annotate(
    fail_count=Sum(Case(When(state='F', then=1), output_field=IntegerField()),
    good_count=Sum(Case(When(state='G', then=1), output_field=IntegerField()),
)

[Kyle Agronick]

Thanks, that looks like it would probably work. I'll throw it in tomorrow and see how it works and let you know.

[Kyle Agronick]

I can't get it to follow the relationship backwards. job_state jobstate Job_state Jobstate, and many other incantations of that relationship. Nothing seems to work:

Here are the relevant models:

from django.db import models
from django.contrib.auth.models import User

job_state = (('P','Pending'), ('A','Active'), ('C','Completed'), ('F','Failed'))
app_type = (('R','RX'),('W','WAR'))

class JobGroup(models.Model):
    created_by = models.ForeignKey(User)
    job_type = models.CharField(max_length=100)
    queued_date = models.DateTimeField(null=True)
    created_date = models.DateTimeField(auto_now_add=True)

class Job(models.Model):
    job_group = models.ForeignKey(JobGroup)
    state = models.CharField(max_length=1, choices=job_state, default='P')
    start_date = models.DateTimeField(null=True)
    end_date = models.DateTimeField(null=True)
    store = models.CharField(max_length=40)
    payload = models.TextField()

    def get_full_state(self):
        for i in job_state:
            if i[0] == self.state:
                return i[1]

        return 'Unknown'

Here is the query:

        qs = JobGroup.objects.all() \
             .select_related('created_by__userprofile__common_name',) \
             .annotate( \
                fail_count=Sum(Case(When(job_state='F', then=1), output_field=IntegerField())),
                good_count=Sum(Case(When(job_state='G', then=1), output_field=IntegerField())),
                store_count=Sum(Case(When(job_state != '0', then=1), output_field=IntegerField())), )

(Although store count should just return a count of job instances related to the job group, I has it relate to state as well as to not complicate things.)

This is everything I ended up adding to the original query. I'm not sure if all this can be done:

        qs = JobGroup.objects.all() \
             .select_related('created_by__userprofile__common_name') \
             .annotate(good_count=RawSQL("""
             IF(jobs_jobgroup.queued_date IS NULL,
                 coalesce((SELECT count(state) FROM supportlink.jobs_job WHERE state = 'C'
                 and job_group_id  = jobs_jobgroup.id GROUP BY state), 0)
             ,
                 "Queued"
             )
             """, []) \
             ,bad_count=RawSQL("""
             IF(jobs_jobgroup.queued_date IS NULL,
                coalesce((SELECT count(state) FROM supportlink.jobs_job WHERE \
                state = 'F' and job_group_id  = jobs_jobgroup.id GROUP BY state), 0)
             ,
                "Queued"
             )""", []) \
             ,store_count=RawSQL("coalesce((SELECT count(job_group_id) FROM supportlink.jobs_job WHERE \
             job_group_id  = jobs_jobgroup.id GROUP BY job_group_id), 0)", []))

[Simon Charette]

Hi agronick,

From your question I suspect you're not comfortable with ​querying backward relationships and ​negating a lookup.

I suggest you define an explicit related_name='jobs' on the ForeignKey pointing to JobGroup and you pass a ~Q(jobs__state='0') object to define your store_count aggregate.

Please use our support channels at TicketClosingReasons/UseSupportChannels for further help as the report doesn't show you have to resort to QuerySet.extra() here.