Advanced Queries

This guide covers advanced querying techniques for localized fields including filtering, ordering, annotations, and aggregations.

Query Expressions

L() - Localized Reference

L() is a shorthand function for creating localized references in queries:

from i18n_fields import L

# Order by current language
articles = Article.objects.order_by(L('title'))

# Order by specific language
articles = Article.objects.order_by(L('title', 'en'))

# Descending order
articles = Article.objects.order_by(L('title').desc())

# Multiple fields
articles = Article.objects.order_by(L('category'), L('title'))

LocalizedRef

LocalizedRef is the full class behind L():

from i18n_fields import LocalizedRef

# Equivalent to L('title')
ref = LocalizedRef('title')

# With specific language
ref = LocalizedRef('title', 'en')

Use LocalizedRef for more complex queries:

from django.db.models import Q
from i18n_fields import LocalizedRef

# Complex filtering
articles = Article.objects.filter(
    Q(published=True) &
    Q(**{LocalizedRef('title', 'en'): 'Hello'})
)

Filtering

By Specific Language

Filter using JSON field lookups:

# Exact match
articles = Article.objects.filter(title__en='Hello World')

# Case-insensitive
articles = Article.objects.filter(title__en__iexact='hello world')

# Contains
articles = Article.objects.filter(title__en__icontains='hello')

# Starts with
articles = Article.objects.filter(title__en__istartswith='hello')

# Ends with
articles = Article.objects.filter(title__en__iendswith='world')

Multiple Languages

Filter by multiple languages:

# Articles with both English and Spanish titles
articles = Article.objects.filter(
    title__en__isnull=False,
    title__es__isnull=False
)

# Articles where English and Spanish match
articles = Article.objects.filter(
    title__en='Hello',
    title__es='Hola'
)

Null Checks

Check for missing translations:

# Articles without Spanish translation
articles = Article.objects.filter(title__es__isnull=True)

# Articles with Spanish translation
articles = Article.objects.filter(title__es__isnull=False)

# Articles with any translation
articles = Article.objects.exclude(title__isnull=True)

Complex Queries

Combine multiple conditions:

from django.db.models import Q

# English OR Spanish title contains "world"
articles = Article.objects.filter(
    Q(title__en__icontains='world') |
    Q(title__es__icontains='mundo')
)

# Published AND has English title
articles = Article.objects.filter(
    Q(published=True) &
    Q(title__en__isnull=False)
)

Ordering

By Current Language

from i18n_fields import L

# Ascending
articles = Article.objects.order_by(L('title'))

# Descending
articles = Article.objects.order_by(L('title').desc())

# Multiple fields
articles = Article.objects.order_by(L('category'), L('title'))

By Specific Language

# Order by English title
articles = Article.objects.order_by(L('title', 'en'))

# Order by Spanish title
articles = Article.objects.order_by(L('title', 'es'))

# Multiple languages
articles = Article.objects.order_by(
    L('title', 'en'),
    L('title', 'es')
)

Mixed Ordering

Combine localized and regular fields:

from i18n_fields import L

# Order by date, then by title
articles = Article.objects.order_by('-created_at', L('title'))

# Order by category, then by title in Spanish
articles = Article.objects.order_by('category', L('title', 'es'))

Annotations

Basic Annotations

Add localized values to query results:

from i18n_fields import LocalizedRef

# Annotate with current language
articles = Article.objects.annotate(
    title_text=LocalizedRef('title')
)

for article in articles:
    print(article.title_text)  # Direct access

Specific Language Annotations

# Annotate with multiple languages
articles = Article.objects.annotate(
    title_en=LocalizedRef('title', 'en'),
    title_es=LocalizedRef('title', 'es'),
    title_fr=LocalizedRef('title', 'fr')
)

for article in articles:
    print(f"EN: {article.title_en}")
    print(f"ES: {article.title_es}")
    print(f"FR: {article.title_fr}")

Computed Annotations

Use annotations with other query operations:

from django.db.models import Value, CharField
from django.db.models.functions import Concat
from i18n_fields import LocalizedRef

# Concatenate localized title with suffix
articles = Article.objects.annotate(
    full_title=Concat(
        LocalizedRef('title', 'en'),
        Value(' - Article'),
        output_field=CharField()
    )
)

Values and Values List

With values()

from i18n_fields import L

# Get dictionary of values
data = Article.objects.values(
    'id',
    'created_at',
    title=L('title', 'en')
)

# Result: [{'id': 1, 'created_at': ..., 'title': 'Hello'}]

With values_list()

# Get tuples
titles = Article.objects.values_list(
    L('title', 'en'),
    flat=True
)

# Result: ['Hello', 'World', ...]

Multiple Languages

# Get multiple languages
data = Article.objects.values(
    'id',
    title_en=L('title', 'en'),
    title_es=L('title', 'es')
)

# Result: [
#     {'id': 1, 'title_en': 'Hello', 'title_es': 'Hola'},
#     ...
# ]

Aggregations

Count

from django.db.models import Count

# Count articles with English title
count = Article.objects.filter(
    title__en__isnull=False
).count()

# Count by category with English title
stats = Article.objects.filter(
    title__en__isnull=False
).values('category').annotate(
    count=Count('id')
)

Group By

from django.db.models import Count
from i18n_fields import LocalizedRef

# Group by first letter of title
from django.db.models.functions import Left

stats = Article.objects.annotate(
    title_text=LocalizedRef('title', 'en'),
    first_letter=Left('title_text', 1)
).values('first_letter').annotate(
    count=Count('id')
)

Exists Queries

Subqueries with Exists

from django.db.models import Exists, OuterRef

# Categories with articles having English titles
categories = Category.objects.annotate(
    has_english_articles=Exists(
        Article.objects.filter(
            category=OuterRef('pk'),
            title__en__isnull=False
        )
    )
).filter(has_english_articles=True)

Q Objects

Complex Conditions

from django.db.models import Q

# OR conditions
articles = Article.objects.filter(
    Q(title__en__icontains='django') |
    Q(title__es__icontains='django')
)

# NOT conditions
articles = Article.objects.filter(
    ~Q(title__en='Deprecated')
)

# Nested conditions
articles = Article.objects.filter(
    Q(published=True) &
    (Q(title__en__icontains='python') |
     Q(title__es__icontains='python'))
)

F Objects

Field Comparisons

from django.db.models import F

# Compare localized fields
# Note: Requires extracting values first
from i18n_fields import LocalizedRef

articles = Article.objects.annotate(
    title_en=LocalizedRef('title', 'en'),
    title_es=LocalizedRef('title', 'es')
).filter(
    title_en=F('title_es')  # Where English equals Spanish
)

Raw SQL

When Necessary

For complex queries not supported by the ORM:

# Query specific JSON key
articles = Article.objects.raw(
    "SELECT * FROM myapp_article WHERE title->>'en' = %s",
    ['Hello World']
)

# JSON array aggregation (PostgreSQL)
from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("""
        SELECT
            category_id,
            json_agg(title->'en') as titles
        FROM myapp_article
        GROUP BY category_id
    """)
    results = cursor.fetchall()

Performance Optimization

Indexing

Add indexes for frequently queried languages:

from django.contrib.postgres.indexes import GinIndex
from django.db import models

class Article(models.Model):
    title = LocalizedCharField(max_length=200)

    class Meta:
        # PostgreSQL GIN index for JSON field
        indexes = [
            GinIndex(fields=['title']),
        ]

Only Fetch Needed Data

from i18n_fields import L

# Only get id and English title
articles = Article.objects.values('id', title=L('title', 'en'))

# Use iterator for large querysets
for article in Article.objects.iterator(chunk_size=100):
    process(article)

Bulk Operations

Bulk Create

articles = [
    Article(
        title={'en': f'Article {i}', 'es': f'Artículo {i}'},
        content={'en': f'Content {i}'}
    )
    for i in range(100)
]

Article.objects.bulk_create(articles)

Bulk Update

# Update specific language for all articles
Article.objects.filter(category='tech').update(
    title={'en': 'Tech Article', 'es': 'Artículo Técnico'}
)

Complete Example

Here’s a comprehensive query example:

from django.db.models import Q, Count, Prefetch
from django.utils import translation
from i18n_fields import L, LocalizedRef

# Complex query with filtering, ordering, and prefetching
with translation.override('en'):
    articles = Article.objects.filter(
        Q(published=True) &
        (Q(title__en__icontains='python') |
         Q(title__es__icontains='python'))
    ).select_related(
        'author',
        'category'
    ).prefetch_related(
        Prefetch(
            'comments',
            queryset=Comment.objects.filter(approved=True)
        )
    ).annotate(
        title_text=LocalizedRef('title'),
        comment_count=Count('comments')
    ).order_by(
        '-created_at',
        L('title')
    )

    for article in articles:
        print(f"Title: {article.title_text}")
        print(f"Comments: {article.comment_count}")
        print(f"Author: {article.author.name}")

Best Practices

Use L() for Ordering

Always use L() for ordering by localized fields:

# Good
articles = Article.objects.order_by(L('title'))

# Don't do this
articles = Article.objects.order_by('title')  # Won't work as expected

Filter by Specific Languages

Use language-specific lookups for precise filtering:

# Good
articles = Article.objects.filter(title__en__icontains='python')

# Less precise
articles = Article.objects.filter(title__icontains='python')

Optimize with Select/Prefetch

Always use select_related and prefetch_related:

articles = Article.objects.select_related(
    'author', 'category'
).prefetch_related('tags')

Use values() When Possible

If you don’t need model instances, use values():
```
# Faster
data = Article.objects.values('id', title=L('title', 'en'))
```
Be Careful with Raw SQL

Prefer ORM when possible, but use raw SQL for complex queries:
```
# Only when ORM can't do it
results = Article.objects.raw("SELECT ...")
```

Next Steps

Fields Reference - Complete field reference
Expressions Reference - Expression reference
Basic Usage - Basic usage patterns

Advanced Queries

Query Expressions

L() - Localized Reference

LocalizedRef

Filtering

By Specific Language

Multiple Languages

Null Checks

Complex Queries

Ordering

By Current Language

By Specific Language

Mixed Ordering

Annotations

Basic Annotations

Specific Language Annotations

Computed Annotations

Values and Values List

With values()

With values_list()

Multiple Languages

Aggregations

Count

Group By

Exists Queries

Subqueries with Exists

Related Lookups

Forward Relations

Reverse Relations

Prefetch and Select Related

Optimize Queries

Q Objects

Complex Conditions

F Objects

Field Comparisons

Raw SQL

When Necessary

Performance Optimization

Indexing

Only Fetch Needed Data

Bulk Operations

Bulk Create

Bulk Update

Complete Example

Best Practices

Next Steps