Creating a simple join on a table with django

Question

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

Thanks for everyone who answered - doing the above is probably not possible in django because of the join. If you re-write your query and instead of using a group by with a join you use inner select (select main.id, (select ) from... ) you would be able to use the "extra({select=...}). Raw SQL might be necessary here... — Liz, Jul 20 '10 at 20:21

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

Try this question, it seems similar:

Django equivalent for count and group by

edited May 23 '17 at 12:30

Community

1
1

answered Jul 19 '10 at 13:12

gruszczy

40,948
31
128
181

Thanks, I don't have relations here so the join seems to be out of place. – Liz Jul 19 '10 at 14:20

Aram Dulyan · Answer 2 · 2010-07-20T00:05:48.577

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.

Thanks for the help, I guess that if I want to be efficient the best way is to rewrite my query to use inner selects instead of regular join that django supports. Anyway, seems that direct SQL is the best way. — Liz, Jul 20 '10 at 06:23

Creating a simple join on a table with django

2 Answers2