Django Q Objects for Complex Database Queries

Snippet

Django Q Objects for Complex Database Queries

Django Q objects enable complex OR/AND database queries that cannot be achieved with simple filter() calls. This example demonstrates building dynamic search queries with multiple optional filters, combining lookups with bitwise operators, and using conditional annotations to calculate discounted prices. The related products query showcases advanced matching with annotation and random ordering.

snippet.py
python
from django.db.models import Q, Count, F, Case, When, IntegerField
from django.db.models.functions import Concat
from.models import Product, Category
 
class ProductManager(models.Manager):
    def search_products(self, query, category=None, min_price=None, max_price=None, in_stock=None, is_featured=None):
        """Complex product search with multiple filter conditions."""
        queryset = self.get_queryset()
 
        search_query = Q(name__icontains=query) | \
                       Q(description__icontains=query) | \
                       Q(sku__iexact=query)
 
        if category:
            search_query &= Q(category__slug=category)
 
        price_query = Q()
        if min_price is not None:
            price_query &= Q(price__gte=min_price)
        if max_price is not None:
            price_query &= Q(price__lte=max_price)
 
        stock_query = Q()
        if in_stock is True:
            stock_query = Q(stock_quantity__gt=0)
        elif in_stock is False:
            stock_query = Q(stock_quantity=0)
 
        featured_query = Q(is_featured=True) if is_featured else Q()
 
        combined_query = search_query & price_query & stock_query & featured_query
 
        return queryset.filter(combined_query).select_related('category').annotate(
            category_product_count=Count('category__products'),
            discounted_price=Case(
                When(discount__gt=0, then=F('price') * (1 - F('discount') / 100)),
                default=F('price'),
                output_field=IntegerField()
            )
        ).order_by('-is_featured', '-created_at')
 
    def get_related_products(self, product, limit=5):
        """Find related products using category and tag matching."""
        return self.get_queryset().filter(
            Q(category=product.category) | Q(tags__in=product.tags.all()),
            Q(is_active=True) & ~Q(pk=product.pk)
        ).annotate(
            matching_criteria=Count('category') + Count('tags')
        ).order_by('-matching_criteria', '?')[:limit]

django

Breakdown

from django.db.models import Q, Count, F, Case, When, IntegerField

Import Q objects and aggregation/conditional helpers

search_query = Q(name__icontains=query) | Q(description__icontains=query)

Create OR query using pipe operator with case-insensitive contains

if category: search_query &= Q(category__slug=category)

Add AND condition to existing query using &= operator

price_query = Q() if min_price is None and max_price is None else Q(price__gte=min_price) & Q(price__lte=max_price)

Build price range query conditionally

combined_query = search_query & price_query & stock_query & featured_query

Combine all query components with AND logic

discounted_price=Case(When(discount__gt=0, then=F('price') * (1 - F('discount') / 100)), default=F('price'))

Use Case/When for conditional annotation calculation

matching_criteria=Count('category') + Count('tags')

Annotate with count of matching criteria for ranking

order_by('-matching_criteria', '?')

Order by relevance then randomize within same rank

Previous snippet Next snippet

More Expert Python exercises →

From your library

Django Q Objects for Complex Database Queries

Related