CODEWITHSUNDEEP
pythonsqldjangodjango-modelsdjango-orm
Aswin Murugesh
Aswin Murugesh

Reputation: 11070

What is the SQL ''LIKE" equivalent on Django ORM queries?

What is the equivalent of the following SQL statement in Django?

SELECT * FROM table_name WHERE string LIKE pattern;

I tried this:

result = table.objects.filter( pattern in string )

but it didn't work. How can I implement it?

Upvotes: 181

Views: 172711

Answers (7)

MD. SHIFULLAH
MD. SHIFULLAH

Reputation: 1731

SQL "LIKE" equivalent on Django ORM queries:

SQL Query:

SELECT * or any_column_name 
FROM table_name
WHERE string LIKE '%pattern%';

Equivalent Django ORM Query Pattern:

qs = Model.objects.filter(column_name__contains='string_pattern')

Equivalent Django ORM Query Example: If the model is: Book and column is: id, name etc. then the ORM Query will be like below:

qs = Book.objects.filter(name__contains='programming')

Upvotes: 0

falsetru
falsetru

Reputation: 369074

Use __contains or __icontains (case-insensitive):

result = table.objects.filter(string__contains='pattern')

The SQL equivalent is

SELECT ... WHERE string LIKE '%pattern%';

@Dmitri's answer below covers patterns like 'pattern%' or '%pattern'

Upvotes: 329

Dmitriy Kuznetsov
Dmitriy Kuznetsov

Reputation: 891

contains and icontains mentioned by falsetru make queries like SELECT ... WHERE headline LIKE '%pattern%

Along with them, you might need these ones with similar behavior: startswith, istartswith, endswith, iendswith

making

SELECT ... WHERE headline LIKE 'pattern%

or

SELECT ... WHERE headline LIKE '%pattern

Upvotes: 57

Omer Anisfeld
Omer Anisfeld

Reputation: 1302

full example : lets say we have table called DjangTable with string field name file_name and we want to create Django filter equivalent to the query that match space in the string file_name in mysql:

SELECT * FROM DjangTable WHERE file_name LIKE '% %' 
class DjangTable(UTModel):


    ...
    file_name = models.CharField(max_length=255, null=False)
    ...

in Django using python it will be :

pattern = ' ' # same as mysql LIKE '% %'
DjangTable.objects.filter(file_name__contains=pattern)

Upvotes: 3

Petr Dlouhý
Petr Dlouhý

Reputation: 927

This can be done with Django's custom lookups. I have made the lookup into a Django-like-lookup application. After installing it the __like lookup with the % and _ wildcards will be enabled.

All the necessary code in the application is:

from django.db.models import Lookup
from django.db.models.fields import Field


@Field.register_lookup
class Like(Lookup):
    lookup_name = 'like'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return '%s LIKE %s' % (lhs, rhs), params

Upvotes: 31

Roderich25
Roderich25

Reputation: 76

In order to preserve the order of the words as in the sql LIKE '%pattern%' statement I use iregex, for example:

qs = table.objects.filter(string__iregex=pattern.replace(' ', '.*'))

string methods are immutable so your pattern variable will not change and with .* you'll be looking for 0 or more occurrences of any character but break lines.

By using the following to iterate over the pattern words: 

qs = table.objects
for word in pattern.split(' '):
    qs = qs.filter(string__icontains=word)

the order of the words in your pattern will not be preserved, for some people that could work but in the case of trying to mimic the sql like statement I'll use the first option.

Upvotes: 5

Venkat Kotra
Venkat Kotra

Reputation: 10753

result = table.objects.filter(string__icontains='pattern')

Case insensitive search for string in a field.

Upvotes: 9

Related Questions