#25977: Allow different filters on multiple aggregates
-------------------------------------+-------------------------------------
Reporter: agronick | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by agronick):
I can't get it to follow the relationship backwards. job_state job__state
Job_state Job__state, 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)",
[]))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25977#comment:6>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/066.7357d86b6a8f77a287b2a817c42b857e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
