django complex query filter

Question

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.

che · Accepted Answer · 2012-08-06 21:20:48Z

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])

answered Aug 6, 2012 at 21:20

che

12.3k7 gold badges47 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marianobianchi · Accepted Answer · 2012-08-07 00:36:26Z

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))

answered Aug 7, 2012 at 0:36

marianobianchi

8,5382 gold badges23 silver badges24 bronze badges

1 Comment

kothvandir Over a year ago

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.

millerdev · Accepted Answer · 2018-01-18 02:23:20Z

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

answered Jan 18, 2018 at 2:23

millerdev

10.4k3 gold badges34 silver badges28 bronze badges

Collectives™ on Stack Overflow

django complex query filter

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related