Creating a simple join on a table with django

Question

Hi,

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

Answer 1

Try this question, it seems similar:

http://stackoverflow.com/questions/327807/django-equivalent-for-count-and-group-by

Answer 2

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.