3

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.

kothvandir
  • 2,111
  • 2
  • 19
  • 34

3 Answers3

4

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])
che
  • 12,097
  • 7
  • 42
  • 71
3

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))
marianobianchi
  • 8,238
  • 1
  • 20
  • 24
  • Yes, it would work with this filter, but i have to apply other functions in other parts of the application, and it would be more flexible to use the "extra" filter over the queryset. Thank you for your time. – kothvandir Aug 07 '12 at 11:52
0

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

millerdev
  • 10,011
  • 2
  • 31
  • 27