I have for now two serach field(planning to add more) and I want to use only raw sql whem filtering the objects(I know that Django provides ORM which makes life so much easier, but in my work right now I need to use SQL) I know how to do it with ORM in Django,like this:
def my_view(request):
value_one = request.GET.get("value_one", None)
value_two = request.GET.get("value_two", None)
value_three = request.GET.get("value_three", None)
objects = MyModel.objects.all()
if value_one:
objects = objects.filter(field_one=value_one)
if value_two:
objects = objects.filter(field_two=value_two)
if value_three:
objects = objects.filter(field_three=value_three)
But is there a way to use SQL instead of filtering?
def profile(request):
cursor = connection.cursor()
value_one = request.GET.get("searchage", None)
value_two = request.GET.get("search", None)
objects= cursor.execute('SELECT * from People p JOIN Jobs j on p.JobId = j.Id ')
objects = dictfetchall(cursor)
if value_one:
objects = cursor.execute('SELECT * from People p JOIN Jobs j on p.JobId = j.Id WHERE p.Age = %s',[value_one] )
if value_two:
objects = ???
return render(request,'personal.html', {'value1':value_one, 'value2':value_two, 'objects':objects})
I have only one idea and it's to have if clause like this
if value_one and value_two:
..
elif value_one and !value_two:
...
elif !value_one and value_two:
But if I have more than 2 search fields it gets kind of difficult and time-consuming to write every clause. Is there another way out without Django ORM?
you can combine all the conditions you want into one sql statement
SELECT * from People p
JOIN Jobs j on p.JobId = j.Id
where (p.Age = [value_one] or [value_one] is null)
and (p.birthdate = [value_two] or [value_two] is null)
and ...
so this way you don't need have several sql command and checking the values :
def profile(request):
cursor = connection.cursor()
value_one = request.GET.get("searchage", None)
value_two = request.GET.get("search", None)
objects= cursor.execute(<query above>)
objects = dictfetchall(cursor)
return render(request,'personal.html', {'value1':value_one, 'value2':value_two, 'objects':objects})