I have a data model in django called MainData which was created on top of a table called "my_data". I want to perfom a simple calculation on top of this table via django API. The query is as follow:
select main.id,
sum(main.num - secondary.num) as result
from (select * from my_data
where some_value > 10) as main,
my_data as secondary
where
main.id != secondary.id and
main.a > secondary.a
group by main.id
The MainData model has all the relevant fields (num, id, a and some_value). How can I implement this query via django? (I'm trying to avoid using direct开发者_运维问答 SQL)
Thanks for the help
Try this question, it seems similar:
Django equivalent for count and group by
That's a tough one. Django doesn't really provide an easy way to join a table with itself without resorting to SQL. Here's the best I can come up with without using SQL. The results returned in the results
variable are equivalent to those you would get with the SQL query you provided; however, the execution is somewhat less efficient.
from django.db.models import Sum, Count
from your_app.models import MyData
results = []
for m in MyData.objects.filter(some_value__gt=10):
result = MyData.objects.filter(a__lt=m.a).aggregate(
count=Count('num'), sum=Sum('num'))
if result['count']:
results.append({
'id': m.id,
'result': result['count'] * m.num - result['sum']})
print results
You should also have a look at the extra()
and raw()
methods Django provides for QuerySets
, which would allow you to make complex queries such as yours efficiently while still keeping things Djangoesque.
精彩评论