CODEWITHSUNDEEP
pythonsqldjango
Nikolai Golub
Nikolai Golub

Reputation: 3567

Django join multiple annotated querysets

I have a following models for simple application:

class App(models.Model):
    name = models.CharField(max_length=60)

class TestCase(models.Model):
    title = models.CharField(max_length=500)
    app = models.ForeignKey('apps.App', blank=True)

class Run(models.Model):
    timestamp = models.DateTimeField()
    value = models.FloatField()
    test_case = models.ForeignKey(TestCase)

And for example I want to calculate for executed and total cases in specific run:

>>> # r is instance of Run
>>> cases_ids = r.values('test_case').distinct()
>>> executed_cases = TestCase.objects.filter(pk__in=cases_ids)
>>> executed_cases_stat = executed_cases.values('app__name').annotate(dcound=Count('app__name'))
[{'executed_cases': 1, 'app__name': u'App1'}, {'executed_cases': 3, 'app__name': u'App2'}]
>>> all_cases_stat = TestCase.objects.all().values('app__name').annotate(total_cases='app__name')
[{'total_cases': 5, 'app__name': u'App1'}, {'total_cases': 7, 'app__name': u'App2'}, {'total_cases': 12, 'app__name': u'App3'}]
>>>
>>> # I want to get this result:
[{'executed_cases': 1, 'total_cases': 5, 'app__name': u'App1'}, {'executed_cases': 3, 'total_cases': 7, 'app__name': u'App2'}, {'executed_cases': 0, 'total_cases': 12, 'app__name': u'App3'}]

I know that it's possible via SQL(it may looks ugly):

select 
    simple_app.name
    , tc.total_cases
    , ce.cases_executed
from apps_app
join (
   select count(id) as total_cases, app_id
   from simple_testcase
    group by app_id) tc 
    on simple_app.id = tc.app_id
join(
    select count(id) as cases_executed, app_id
    from simple_testcase
    where id in (select test_case_id
    from simple_run where run_id = 1)
    group by app_id) ce 
    on apps_app.id = ce.app_id

Is there any way do it without raw sql or iterating in python?

Upvotes: 1

Views: 656

Answers (1)

Serafeim
Serafeim

Reputation: 15084

Most of the times I want to do things that seem impossible with annotations, I use extra (https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.extra)

for instance, in your case you could do something like this:

apps_extra = App.objects.all().extra (select={
    'total_cases': """
      select count(id) 
      from simple_testcase
      where simple_app.id = simple_testcase.app_id
    """, 
    'executed_cases': """
      # the same with your second nested query
    """,
})

Now the apps_extra will contain App objects with total_cases and executed_cases fields !

This is really great because you can use apps_extra as it is as a Column() source to django_tables2 and enable sorting on that column !

Upvotes: 3

Related Questions