How do I do general math in a sql query in django?
The next query I would like to make in django, ideally without using iteration. I just want the database call to return the result indicated below. Unfortunately, according to the docs , this is not possible; only common features are available, such as Avg
, Max
and Min
etc. I'm currently using django 1.4, but I'm happy to rewrite stuff with django 1.8 (hence, on the docs page, I've heard that 1.8 does a lot better than 1.4)
select sum(c.attr1 * fs.attr2)/ sum(c.attr1) from fancyStatistics as fs
left join superData s on fs.super_id=s.id
left join crazyData c on s.crazy_id=c.id;
Note:
The main reason for this in django is that if we ever want to change our database from MySQL to something more suitable for django, it would be nice not to rewrite all the queries.
source to share
You should be able to get aggregates with F expressions in order to do most of what you want without dropping the SQL.
https://docs.djangoproject.com/en/1.8/topics/db/aggregation/#joins-and-aggregates
aggregate_dict = FancyStatistics.objects.all()\
.aggregate(
sum1=Sum(
F('superdata__crazydata__attr1') * F('attr2'), output_field=FloatField()
) ,
sum2=Sum('superdata__crazydata__attr1')
)
)
result = aggregate_dict['sum1'] / aggregate_dict['sum2']
You need to specify the output fields if the data types used are different.
source to share
You can make this query in Django directly using an SQL expression. Check out the docs regarding raw SQL execution .
source to share