[Django] #25977: Allow different filters on multiple aggrigates
Django
-------------------------------+----------------------------
Reporter: agronick | Owner: nobody
Type: Bug | Status: new
Component: Uncategorized | Version: 1.9
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+----------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25977>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
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
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: => 0
* component: Uncategorized => Database layer (models, ORM)
* needs_tests: => 0
* needs_docs: => 0
* type: Bug => New feature
Old description:
> 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.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25977#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: agronick | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/25977#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: agronick | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by charettes):
Does the following [https://docs.djangoproject.com/en/1.9/ref/models
/conditional-expressions/#conditional-aggregation conditional aggregation]
works for you?
{{{#!python
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()),
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25977#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: agronick | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by agronick):
Thanks, that looks like it would probably work. I'll throw it in tomorrow
and see how it works and let you know.
--
Ticket URL: <https://code.djangoproject.com/ticket/25977#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: agronick | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* cc: charettes (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/25977#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: agronick | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: 1.9
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
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
-------------------------------------+-------------------------------------
Reporter: agronick | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 1.9
(models, ORM) |
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* status: new => closed
* resolution: => invalid
Comment:
Hi agronick,
From your question I suspect you're not comfortable with
[https://docs.djangoproject.com/en/1.9/topics/db/queries/#following-
relationships-backward querying backward relationships] and
[https://docs.djangoproject.com/en/1.9/topics/db/queries/#complex-lookups-
with-q-objects 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25977#comment:7>