django custom Func for specific SQL function

Question

I'm currently performing a raw query in my database because i use the MySQL function instr. I would like to translate is into a django Func class.
I've spend several days reading the docs, Django custom for complex Func (sql function) and Annotate SQL Function to Django ORM Queryset `FUNC` / `AGGREGATE` but i still fail to write succesfully my custom Func.
This is my database

from django.db import models
class Car(models.Model):
        brand = models.CharField("brand name", max_length=50)
#then add the __str__ function

Then I populate my database for this test

Car.objects.create(brand="mercedes")
Car.objects.create(brand="bmw")
Car.objects.create(brand="audi")

I want to check if something in my table is in my user input. This is how i perform my SQL query currently

query = Car.objects.raw("SELECT * FROM myAppName_car WHERE instr(%s, brand)>0", ["my audi A3"])
# this return an sql query with one element in this example

I'm trying to tranform it in something that would look like this

from django.db.models import Func
class Instr(Func):
        function = 'INSTR'

query = Car.objects.filter(brand=Instr('brand'))

EDIT

Thank to the response, the correct answer is

from django.db.models import Value
from django.db.models.functions import StrIndex
query = Car.objects.annotate(pos=StrIndex(Value("my audi A3"), "brand")).filter(pos__gt=0)

icontains and contains don't fit my needs because the user input contain some database entry and not the other direction — spystrach
– spystrach, Commented May 6, 2021 at 16:11

GwynBleidD · Accepted Answer · 2021-05-06 00:50:26Z

0

Your custom database function is totally correct, but you're using it in the wrong way.

When you look at your usage in the raw SQL function, you can clearly see that you need 3 parameters for your filtering to work correctly: a string, a column name and a threshold (in your case it is always zero)

If you want to use this function in the same way in your query, you should do it like this:

query = Car.objects.annotate(
    position_in_brand=Instr("my audi A3")
).filter(position_in_brand__gt=0)

For this to work properly, you also need to add output_field = IntegerField() to your function class, so Django knows that the result will always be an integer.

But... You've missed 2 things and your function is not needed at all, because it already exist in Django as StrIndex.

And the 2nd thing is: you can achieve the same outcome by using already existing django lookups, like contains or icontains:

query = Car.objects.filter(brand__contains="my audi A3")

You can find out more about lookups in Django docs

answered May 6, 2021 at 0:50

GwynBleidD

20.6k5 gold badges51 silver badges80 bronze badges

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

2 Comments

spystrach Over a year ago

thx for your response, first icontains don't fit my needs because i want to check if there are somme database brand "bmw", "audi" or "mercedes"contained inside the user input "my audi A3" and not the other direction.

GwynBleidD Over a year ago

There are also other solutions to achieve that using contains or icontains. You can also create your own lookup that will work in reverse.

Collectives™ on Stack Overflow

django custom Func for specific SQL function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related