Django Field Lookups Reference

Query database with powerful lookup expressions

🔍 What are Field Lookups?

Field lookups are special keywords used in Django ORM queries to filter database records. They follow the field__lookup syntax and enable complex queries like contains, greater than, or case-insensitive matching without writing raw SQL. 


# Basic field lookup examples
User.objects.filter(age__gte=18)  # Age >= 18
Post.objects.filter(title__icontains='django')  # Case-insensitive
Product.objects.filter(price__lt=100)  # Price < 100

Lookup Categories

🎯

Exact Match

Exact and case-insensitive matching 

filter(name__exact='John')
filter(email__iexact='[email protected]')
📊

Comparisons

Greater than, less than operations 

filter(age__gt=18)
filter(price__lte=50)
🔤

String Patterns

Contains, starts with, ends with 

filter(title__contains='Django')
filter(name__startswith='A')
📅

Date Lookups

Date and time filtering 

filter(created__year=2024)
filter(date__month=1)

🔹 Exact Match Lookups

Exact match lookups find records where field values match exactly or case-insensitively. The default lookup is 'exact' when no lookup is specified. 

# models.py
from django.db import models

class User(models.Model):
    username = models.CharField(max_length=100)
    email = models.EmailField()
    is_active = models.BooleanField(default=True)

# Exact match (case-sensitive)
User.objects.filter(username__exact='john')
User.objects.filter(username='john')  # Same as above

# Case-insensitive exact match
User.objects.filter(email__iexact='[email protected]')
# Matches: [email protected], [email protected], [email protected]

# Boolean exact match
User.objects.filter(is_active__exact=True)
User.objects.filter(is_active=True)  # Same as above

When to Use:

  • exact - Case-sensitive exact match (default)
  • iexact - Case-insensitive exact match (emails, usernames)

🔹 Comparison Lookups

Comparison lookups filter records using mathematical comparisons like greater than, less than, and range checks for numeric and date fields. 

# models.py
class Product(models.Model):
    name = models.CharField(max_length=200)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    stock = models.IntegerField()

# Greater than
Product.objects.filter(price__gt=50)  # price > 50

# Greater than or equal
Product.objects.filter(stock__gte=10)  # stock >= 10

# Less than
Product.objects.filter(price__lt=100)  # price < 100

# Less than or equal
Product.objects.filter(stock__lte=5)  # stock <= 5

# Range (inclusive)
Product.objects.filter(price__range=(20, 50))  # 20 <= price <= 50

# Combining comparisons
Product.objects.filter(price__gte=10, price__lte=100)
# Same as: Product.objects.filter(price__range=(10, 100))

Example Query Results:

price__gt=50 → Products with price > $50

stock__lte=5 → Products with 5 or fewer items

price__range=(20, 50) → Products between $20-$50

🔹 String Pattern Lookups

String pattern lookups search for partial matches in text fields using contains, starts with, ends with, and their case-insensitive variants. 

# models.py
class Article(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()
    author = models.CharField(max_length=100)

# Contains (case-sensitive)
Article.objects.filter(title__contains='Django')
# Matches: "Django Tutorial", "Learning Django"

# Case-insensitive contains
Article.objects.filter(title__icontains='python')
# Matches: "Python", "PYTHON", "python basics"

# Starts with
Article.objects.filter(author__startswith='John')
# Matches: "John Smith", "Johnny"

# Case-insensitive starts with
Article.objects.filter(title__istartswith='how')
# Matches: "How to...", "HOW TO...", "how to..."

# Ends with
Article.objects.filter(title__endswith='Guide')
# Matches: "Beginner's Guide", "Complete Guide"

# Case-insensitive ends with
Article.objects.filter(title__iendswith='tutorial')
# Matches: "Django Tutorial", "PYTHON TUTORIAL"

Pattern Lookup Types:

  • contains / icontains - Substring anywhere in text
  • startswith / istartswith - Text begins with pattern
  • endswith / iendswith - Text ends with pattern
  • Prefix with 'i' for case-insensitive matching

🔹 Date and Time Lookups

Date lookups extract specific parts of datetime fields like year, month, day, hour for filtering records by time periods and components. 

# models.py
from django.db import models

class Post(models.Model):
    title = models.CharField(max_length=200)
    created_at = models.DateTimeField(auto_now_add=True)
    published_date = models.DateField()

# Year lookup
Post.objects.filter(created_at__year=2024)

# Month lookup (1-12)
Post.objects.filter(created_at__month=1)  # January

# Day lookup
Post.objects.filter(created_at__day=15)

# Week day (1=Sunday, 7=Saturday)
Post.objects.filter(created_at__week_day=2)  # Monday

# Date exact
Post.objects.filter(published_date__date='2024-01-15')

# Time lookups
Post.objects.filter(created_at__hour=14)  # 2 PM
Post.objects.filter(created_at__minute=30)
Post.objects.filter(created_at__second=0)

# Combining date lookups
Post.objects.filter(
    created_at__year=2024,
    created_at__month=1,
    created_at__day__gte=15
)
# Practical examples
# Posts from this year
Post.objects.filter(created_at__year=2024)

# Posts from January
Post.objects.filter(created_at__month=1)

# Posts created on Mondays
Post.objects.filter(created_at__week_day=2)

# Posts from Q1 2024
Post.objects.filter(
    created_at__year=2024,
    created_at__month__lte=3
)

🔹 Null and Boolean Lookups

Special lookups for checking null values and boolean fields. Use isnull to find empty fields and direct comparison for boolean values. 

# models.py
class Profile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    bio = models.TextField(null=True, blank=True)
    avatar = models.ImageField(null=True, blank=True)
    is_verified = models.BooleanField(default=False)

# Check for null values
Profile.objects.filter(bio__isnull=True)  # No bio
Profile.objects.filter(bio__isnull=False)  # Has bio

# Check for empty or null
Profile.objects.filter(avatar__isnull=True)  # No avatar

# Boolean lookups
Profile.objects.filter(is_verified=True)  # Verified users
Profile.objects.filter(is_verified=False)  # Unverified users

# Combining null checks
Profile.objects.filter(
    bio__isnull=False,
    is_verified=True
)  # Verified users with bio

Important Notes:

  • Use isnull=True to find NULL database values
  • Empty strings ('') are NOT null
  • For boolean fields, use direct comparison: field=True

🔹 In and Range Lookups

In lookup checks if field value exists in a list. Range lookup filters values between two boundaries inclusively for efficient multi-value queries. 

# models.py
class Order(models.Model):
    status = models.CharField(max_length=20)
    total = models.DecimalField(max_digits=10, decimal_places=2)
    quantity = models.IntegerField()

# In lookup - check if value in list
Order.objects.filter(status__in=['pending', 'processing', 'shipped'])

# In with numbers
Order.objects.filter(quantity__in=[1, 5, 10, 20])

# Range lookup (inclusive)
Order.objects.filter(total__range=(50, 200))  # 50 <= total <= 200

# Date range
from datetime import date
start_date = date(2024, 1, 1)
end_date = date(2024, 12, 31)
Order.objects.filter(created_at__range=(start_date, end_date))

# Exclude with in
Order.objects.exclude(status__in=['cancelled', 'refunded'])
# Practical examples
# Active orders
Order.objects.filter(status__in=['pending', 'processing'])

# Medium-sized orders
Order.objects.filter(quantity__range=(5, 20))

# Orders from Q1
Order.objects.filter(
    created_at__range=('2024-01-01', '2024-03-31')
)

🔹 Regex Lookups

Regular expression lookups enable complex pattern matching using regex syntax. Useful for advanced text searches and validation in database queries. 

# models.py
class Customer(models.Model):
    name = models.CharField(max_length=100)
    phone = models.CharField(max_length=20)
    email = models.EmailField()

# Regex (case-sensitive)
Customer.objects.filter(phone__regex=r'^\d{3}-\d{3}-\d{4}$')
# Matches: 123-456-7890

# Case-insensitive regex
Customer.objects.filter(email__iregex=r'^[a-z]+@gmail\.com$')
# Matches: [email protected], [email protected]

# Find names with numbers
Customer.objects.filter(name__regex=r'\d')

# Find emails from specific domains
Customer.objects.filter(email__iregex=r'@(gmail|yahoo|hotmail)\.com$')

# Phone numbers starting with area code
Customer.objects.filter(phone__regex=r'^(555|666)')

# Names with special characters
Customer.objects.filter(name__regex=r'[^a-zA-Z\s]')

Common Regex Patterns:

  • ^\d{3}$ - Exactly 3 digits
  • ^[A-Z] - Starts with uppercase letter
  • \d+ - One or more digits
  • [a-z]+@ - Lowercase letters before @

🔹 Related Field Lookups

Span relationships using double underscores to query related models. Access foreign key and many-to-many fields through relationship chains. 

# models.py
class Author(models.Model):
    name = models.CharField(max_length=100)
    country = models.CharField(max_length=50)

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    published_date = models.DateField()
    price = models.DecimalField(max_digits=10, decimal_places=2)

# Query through foreign key
Book.objects.filter(author__name='John Doe')

# Multiple levels deep
Book.objects.filter(author__country='USA')

# Combining related lookups
Book.objects.filter(
    author__name__icontains='smith',
    price__lt=30
)

# Related field with date lookup
Book.objects.filter(
    author__name='Jane Doe',
    published_date__year=2024
)

# Reverse relationship
Author.objects.filter(book__title__icontains='Django')
Author.objects.filter(book__price__gte=50)
# Complex related queries
# Books by US authors published in 2024
Book.objects.filter(
    author__country='USA',
    published_date__year=2024
)

# Authors with expensive books
Author.objects.filter(book__price__gte=100).distinct()

# Books by authors whose name starts with 'J'
Book.objects.filter(author__name__istartswith='j')

🔹 Combining Lookups with Q Objects

Q objects enable complex queries with OR logic and negation. Combine multiple conditions using & (AND), | (OR), and ~ (NOT) operators. 

from django.db.models import Q

# OR queries
Product.objects.filter(
    Q(name__icontains='phone') | Q(name__icontains='tablet')
)

# AND with OR
Product.objects.filter(
    Q(price__lt=100) & (Q(category='Electronics') | Q(category='Gadgets'))
)

# NOT queries
Product.objects.filter(~Q(status='discontinued'))

# Complex combinations
Product.objects.filter(
    Q(price__gte=50) & Q(price__lte=200) |
    Q(on_sale=True)
)

# Nested Q objects
Product.objects.filter(
    Q(name__icontains='laptop') &
    (Q(brand='Dell') | Q(brand='HP')) &
    Q(price__lt=1000)
)
# Practical examples
# Search products
search_term = 'phone'
Product.objects.filter(
    Q(name__icontains=search_term) |
    Q(description__icontains=search_term)
)

# Active users from specific countries
User.objects.filter(
    Q(is_active=True) &
    Q(country__in=['USA', 'Canada', 'UK'])
)

🔹 Field Lookup Summary

Lookup Reference:

  • exact / iexact - Exact match (case-sensitive/insensitive)
  • contains / icontains - Substring match
  • startswith / istartswith - Starts with pattern
  • endswith / iendswith - Ends with pattern
  • gt / gte / lt / lte - Comparison operators
  • in - Value in list
  • range - Between two values (inclusive)
  • isnull - Check for NULL values
  • regex / iregex - Regular expression match
  • year / month / day - Date component extraction

🧠 Test Your Knowledge

Which lookup finds records where age is 18 or greater?