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)