CODEWITHSUNDEEP
djangodjango-queryset
Drwhite
Drwhite

Reputation: 1685

How to add more conditions in where clause?

I have made a loop to retrieve conditions:

for level in levels:
    requete += ' and level_concat like %'+level+'%'

and i made in my query:

countries = Country.objects.extra(where=['continent_id = "'+continent_id+'"', requete])

I have tried to add condition to my where clause, but return error:

not enough arguments for format string

Respected results:

SELECT * FROM `Country` WHERE country_id = "US-51" AND level_concat LIKE %level_test%

Is there a way to add requete to my 'where' clause?

Upvotes: 0

Views: 1101

Answers (2)

Ludwik Trammer
Ludwik Trammer

Reputation: 25022

Firstly, it is not a good practice to keep data in a relational database as a "list of [values] concatenated by coma" - you should create a new table for those values.

Still, even now you can use filter(), instead of extra() (which should be always your last resort - I don't see the rest of your code, but if you don't properly escape levels values you may even be introducing an SQL Injection vulnerability here).

An example of a secure, extra()-less code, that does the exact same thing:

from django.db.models import Q

q = Q()
for level in levels:
    q &= Q(level_concat__contains=level)

countries = Country.objects.filter(q)

or the same functionality, but in even less number of lines:

from django.db.models import Q

q = (Q(level_concat__contains=l) for l in levels)
countries = Country.objects.filter(*q)

You can read more about Q object in Django docs.

Upvotes: 1

Aamir Rind
Aamir Rind

Reputation: 39659

I think you need to escape the % sign in your query:

' and level_concat like %%'+level+'%%'

Upvotes: 0

Related Questions