Opened 9 years ago

Closed 9 years ago

#25977 closed New feature (invalid)

Allow different filters on multiple aggregates

Reported by: Kyle Agronick Owned by: nobody
Component: Database layer (models, ORM) Version: 1.9
Severity: Normal Keywords: QuerySet.extra
Cc: Simon Charette Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

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.

Change History (7)

comment:1 by Tim Graham, 9 years ago

Component: UncategorizedDatabase layer (models, ORM)
Description: modified (diff)
Type: BugNew feature

comment:2 by Tim Graham, 9 years ago

Summary: Allow different filters on multiple aggrigatesAllow different filters on multiple aggregates

comment:3 by Simon Charette, 9 years ago

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()),
)

comment:4 by Kyle Agronick, 9 years ago

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

comment:5 by Simon Charette, 9 years ago

Cc: Simon Charette added

comment:6 by Kyle Agronick, 9 years ago

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)", []))

comment:7 by Simon Charette, 9 years ago

Resolution: invalid
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top