Django ORM: combining aggregate queries into one
Can these two queries be linked into one?
qs1 = OrderTicket.objects.filter(date__gt=datetime.date(2009, 1, 1), date__lt=datetime.date(2009, 1, 30)).values('order_type').annotate(value_1 = Sum('gbp_value')).order_by('order_type'),
qs2 = OrderTicket.objects.filter(date__gt=datetime.date(2009, 2, 1), date__lt=datetime.date(2009, 2, 30)).values('order_type').annotate(value_2 = Sum('gbp_value')).order_by('order_type'),
All I want is the columns value_1 and value_2. Q objects are not what I need. Perhaps the ORM doesn't support this.
+2
source to share
2 answers
You can combine queries using | and operators:
# You can combine queries with & and |.
>>> s1 = Article.objects.filter(id__exact=1)
>>> s2 = Article.objects.filter(id__exact=2)
>>> s1 | s2
[<Article: Area woman programs in Python>, <Article: Second article>]
>>> s1 & s2
[]
Source http://www.djangoproject.com/documentation/models/basic/
+2
source to share
I would suggest using Q objects instead of your filter for date__gt and date__lt
Example (not tested):
qs1 = OrderTicket.objects
.filter( Q(date__gt=datetime.date(2009, 1, 1), date__lt=datetime.date(2009, 1, 30))
| Q(date__gt=datetime.date(2009, 2, 1), date__lt=datetime.date(2009, 2, 30))
)
.values('order_type').annotate(value_1 = Sum('gbp_value')).order_by('order_type')
This should return both dates you are looking for.
+1
source to share