CODEWITHSUNDEEP
djangodjango-models
kothvandir
kothvandir

Reputation: 2161

django complex query filter

I need to make a complex filter that implies to apply a function over a model property.

The equivalent SQL query is:

SELECT * FROM myModel_table t WHERE abs(t.field1 - :binding_value) <= 50

where binding_value is a numeric received from the client.

Is there any way to define a field lookup using a funcion? Or do I have to directly use the raw sql?

Thank you.

Upvotes: 3

Views: 1471

Answers (3)

millerdev
millerdev

Reputation: 10331

Filter on an annotation:

from django.db.models import Func

class Abs(Func):
    function = "abs"

MyModel.objects.annotate(
    expr=Abs("field1") - binding_value
).filter(
    expr__lte=50
)

Edit: see also https://stackoverflow.com/a/35658634/10840

Upvotes: 0

marianobianchi
marianobianchi

Reputation: 8488

If i'm not thinking the wrong way (please let me now if i am), you can "translate" you query a little to use simple django filtering.

First, it's True that:

abs(A - B) <= 50

is equal to:

-50 <= A - B <= 50

Then, this is equal to:

-50 + B <= A <= 50 + B

Then, your query could be like this:

MyModel.objects.filter(field1__range=(-50+binding_value,50+binding_value))

Upvotes: 3

che
che

Reputation: 12263

In this case the best way would be to use Django's queryset extra() method:

MyModel.objects.extra(where=['abs(field1 - %s) <= 50'], params=[binding_value])

Upvotes: 4

Related Questions