CODEWITHSUNDEEP
pythondjangoormdjango-queryset
Saqib Ali
Saqib Ali

Reputation: 12545

How can I filter a Django Queryset on an extra field?

Here is my Django model:

from django.db import models
class MyModel(models.Model):
    a = models.IntegerField()
    b = models.IntegerField()
    c = models.IntegerField()

I want to retrieve all instances of this model in which a = 5 and b + c > 10. How can I do it?

When I try this:

print MyModel.objects.filter(a=5).extra(
    select={"total_count": "b + c"},
    where=["total_count > 10"],
)

I get this error:

OperationalError: (1054, "Unknown column 'total_count' in 'where clause'")

Upvotes: 5

Views: 4986

Answers (2)

levi
levi

Reputation: 22697

You can transform b + c > 10 to b > 10 - c and then use F built-in function

MyModel.objects.filter(a=5).filter((b__gt=10-models.F('c'))

Using Django extra() is not quite safe

You should be very careful whenever you use extra(). Every time you use it, you should escape any parameters that the user can control by using params in order to protect against SQL injection attacks . Please read more about SQL injection protection.

Upvotes: 2

catavaran
catavaran

Reputation: 45575

In django 1.7 this filter works fine and produces the following query:

SELECT (b + c) AS "total_count", "app_mymodel"."id", "app_mymodel"."a",
       "app_mymodel"."b", "app_mymodel"."c" 
FROM "app_mymodel"
WHERE ("app_mymodel"."a" = 5 AND (total_count > 10))

Can you copy your query with real field names? May be you mistyped somewhere?

Upvotes: 1

Related Questions