Implementing SQL LIKE Queries with Django ORM
Last Updated :
13 Aug, 2024
When working with databases, one often comes across the necessity to do the search by pattern contained within the textual field. In SQL this can be done with the help of the LIKE operator which allows to search of the given pattern in the column.
Moving forward in this article, we will aim to explain the ways in which a typical Django ORM SQL LIKE can be performed by utilizing '_icontains' and '_contains' lookups.
Using '_icontains' for Case-Insensitive Match
The '_icontains' lookup in Django can be set equal to LIKE in SQL with ignoring of case. It enables you to apply a condition to records that does not take into account differences in cases. This is especially important when you want to match for a pattern regardless of the case in which the text is written.
Example:
Suppose you have a Product model with a name field, and you want to find all products whose names contain the substring "phone" irrespective of case:
Python
from myapp.models import Product
products = Product.objects.filter(name__icontains='phone')
This query will return all products where the name contains the word "phone", such as "Smartphone", "Telephone", or "PHoNE".
Output:
<QuerySet [<Product: SmartPhone>, <Product: Telephone>, <Product: PHoNE>]>
How It Works:
'_icontains' translates to SQL's LIKE '%phone%' but ignores case sensitivity. The SQL query generated will look something like:
>>> str(products.query)
'SELECT ... FROM "myapp_product" WHERE "myapp_product"."name" ILIKE %phone%'
Using '_contains' for Case-Sensitive Match
If you require a search that is sensitive to cases sensitivity for instance a search for a string “chuck”, Django has the '_contains' lookup. It appears to be similar to '_icontains' but is applied against the case of the character. Use this when the case of the text is of significance to your pattern matching.
Example:
Using the same Product model, suppose you want to find all products whose names contain the exact substring "Phone" with the first letter capitalized:
Python
products = Product.objects.filter(name__contains='Phone')
This query will return only those products where the name contains "Phone" with an uppercase "P" and lowercase "hone", such as "Phone" or "SmartPhone".
Output:
<QuerySet [<Product: SmartPhone>]>
How It Works:
'_contains' translates to SQL's LIKE '%Phone%' and respects the case. The SQL query generated will look something like:
SELECT ... FROM product WHERE name ILIKE '%Phone%';
Code Example
Here's a complete code example demonstrating both '_icontains' and '_contains' lookups:
In this example, we have a simple Django view that takes a search term from the request and returns two sets of results: one for case-insensitive matching using '_icontains' and another for case-sensitive matching using '_contains'.
Python
from django.shortcuts import render
from myapp.models import Product
def search_products(request):
search_term = request.GET.get('q', '')
# Case-insensitive search
case_insensitive_results = Product.objects.filter(name__icontains=search_term)
# Case-sensitive search
case_sensitive_results = Product.objects.filter(name__contains=search_term)
return render(request, 'search_results.html', {
'search_term': search_term,
'case_insensitive_results': case_insensitive_results,
'case_sensitive_results': case_sensitive_results,
})
Conclusion
By nature, Django’s ORM provides viable algorithm for matching patterns of strings that can be compared to the LIKE operator in SQL. The syntax of __icontains will perform a search that does not consider the case whereas __contains suffers from a case-sensitive search. These tools enable one to execute complex queries accrued from the database without the need for raw sql. These lookups will help you filter results when you are asking for the user’s input, filter product names, or work with other large datasets; these ‘lookups’ give you the necessary tools for implementing powerful search options in your Django applications.
Similar Reads
Extracting SQL Queries from Django QuerySets
Django, a high-level Python web framework, simplifies the development of secure and maintainable websites. One of its powerful features is the Object-Relational Mapping (ORM) system, which allows developers to interact with the database using Python code instead of raw SQL. However, there are times
3 min read
Raw SQL queries in Django views
Let's create a simple Django project that demonstrates the use of raw SQL queries in a view. In this example, we'll create a project to manage a list of books stored in a database. We'll create a view that retrieves books from the database using a raw SQL query and displays them on a webpage. Settin
4 min read
How to Perform Query Filtering in Django Templates
Sometimes we may want to filter or modify the list of objects within the template itself to tailor the data being displayed. While filtering should generally be done at the view level for clarity and separation of concerns, Django templates provide some basic filtering capabilities through template
5 min read
Python Django Queryset Filtering
In Django, QuerySet filtering allows you to retrieve only the data you need from the database. QuerySets help you filter, sort and organize your data efficiently, making it easy to interact with your models.This article will explore how to filter datasets using Djangoâs filter(), exclude() and advan
2 min read
Getting started with Django
Python Django is a web framework that is used to create web applications very efficiently and quickly. Django is called a battery included framework because it contains a lot of in-built features such as Django Admin Interface, default database - SQLite3, etc. Django provides various ready-made comp
15+ min read
Writing and Executing Queries in GraphQL
GraphQL has developed into an effective API language that offers developers more effective and adaptable ways to work with data. By enabling clients to request only the data they require, GraphQL, in contrast with traditional REST APIs, removes over- and under-conditioning. In this article, we are g
7 min read
Django return redirect() with Parameters
When building web applications with Django, it's common to need to redirect users to different pages, often passing parameters to specify where and with what context the redirect should occur. Django provides a powerful and flexible way to handle redirects using the Django redirect() function, which
3 min read
Intermediate fields in Django | Python
Prerequisite: Django models, Relational fields in DjangoIn Django, a many-to-many relationship is used when instances of one model can be associated with multiple instances of another model and vice versa. For example, in a shop management system:A Customer can purchase multiple Items.An Item can be
2 min read
Is there a combination of "LIKE" and "IN" in SQL Server?
In SQL Server, the LIKE operator is commonly used for pattern matching within string columns, while the IN operator is utilized to filter data based on a set of specified values. By combining these two operators, we can create more efficient queries. This combination allows for filtering results bas
3 min read
Handling Ajax request in Django
AJAX (Asynchronous JavaScript and XML) is a web development technique that allows a web page to communicate with the server without reloading the entire page. In Django, AJAX is commonly used to enhance user experience by sending and receiving data in the background using JavaScript (or libraries li
3 min read