How to write a query with many ANDs and ORs with sum in Python?

Question

I'm trying to write the following query in Django/Python: in this query, I don't have a simple or, but I have many ands also and a sum

SELECT sum(value) FROM myapp_category
WHERE (name='difficulty' AND key='hard')
   OR (name= 'success' AND key='yes')
   OR (name= 'alternative' AND key='yes')
   OR (name= 'processing' AND key='good')
   OR (name= 'personal_touch' AND key='yes') `

and here's my model:

class Category(models.Model):
   name = models.CharField(max_length=50)
   key = models.CharField(max_length=30)
   value = models.FloatField(blank=True, default=0)

   def __str__(self):
      return self.name.encode('utf_8') + "_" + self.key.encode('utf_8')

and I don't want to use the raw sql, so what can I use for this ?

Update Answer: Thanks for your answers, this is the complete answer:

    sum = Category.objects.filter(Q(name='difficulty',key=evaluation.difficulty) | 
Q(name='nogos',key=evaluation.nogos) | 
Q(name='success',key=evaluation.success) | 
Q(name='alternative',key=evaluation.alternative) | 
Q(name='processing',key=evaluation.processing) | 
Q(name='personal_touch',key=evaluation.personal_touch))
.aggregate(result=Sum('value'))

    score = float(sum['result'])

Do you have any models? Could you post them in your question? — Wtower, Oct 14 '15 at 08:54
Possible duplicate of [Django Filters - or?](http://stackoverflow.com/questions/739776/django-filters-or) — Sayse, Oct 14 '15 at 09:01
in this question, I have not a simple `or`, I have many `and`s also. — Safaa Na, Oct 14 '15 at 09:06
@SafaaNa - Your answer doesn't differ much from the duplicate, as shown in Geo Jacobs answer. — Sayse, Oct 14 '15 at 09:09

score 1 · Accepted Answer · answered Oct 14 '15 at 09:01

1

Try this;

from django.db.models import Q, Sum


Category.objects.filter(Q(name='difficulty',key='hard') | Q(name='success',key='yes') | Q(name='alternative',key='yes') | Q(name='processing',key='good') | Q(name='personal_touch',key='yes')).aggregate(Sum('value'))

answered Oct 14 '15 at 09:01

Geo Jacob

5,909
1
36
43

Thanks for your answer, the output is an array and not a value. are you sure from this `.annotate(Sum('value'))` – Safaa Na Oct 14 '15 at 09:13
In what basis you want to calculate the sum of values. How you want to group it? – Geo Jacob Oct 14 '15 at 09:15
just as the raw query in the question, I put all my condition in the `where` and just a simple `sum` without group will do it. in the end I want to save this sum in another table – Safaa Na Oct 14 '15 at 09:17
I think I found it, using `aggregate ` instead of `annotate` – Safaa Na Oct 14 '15 at 09:27
In which field you are saving, can you show me the table? – Geo Jacob Oct 14 '15 at 09:39
1

I updated the question with the complete query, I googled a little and found away to do it, many thanks for your help – Safaa Na Oct 14 '15 at 09:42

score 0 · Answer 2 · answered Oct 14 '15 at 09:01

0

UPD

from django.db.models import Q

results = Category.objects.filter(
    Q(name="difficulty", key="hard") | Q(name= "success", key="yes") | 
    Q(name="alternative", key="yes")|Q(name="processing" AND key="good") | 
    Q(name="personal_touch",key="yes"))

answered Oct 14 '15 at 09:01

ivan K.

207
1
7

Thanks for you answer but it misses the `sum` part – Safaa Na Oct 14 '15 at 09:33

score 0 · Answer 3 · answered Oct 14 '15 at 09:05

0

Not really a solution but maybe input for other ideas:

SELECT sum(value) FROM myapp_category
WHERE name+"/"+key IN (
  'difficulty/hard',
  'success/yes',
  'alternative/yes',
  'processing/good',
  'personal_touch/yes'
)

Problems:

Also lists cannot be provided using parameters
The query is slower because indexes cannot be used

answered Oct 14 '15 at 09:05

Daniel Alder

5,031
2
45
55

I'm leaving the raw sql as a last solution. – Safaa Na Oct 14 '15 at 09:07

How to write a query with many ANDs and ORs with sum in Python?

3 Answers3