Expressions Reference

This page documents query expressions for localized fields.

LocalizedRef

class LocalizedRef(name, lang=None)

Query expression that selects a value in a specific language from a localized field.

Parameters:

• name (str): The field/column name

• lang (str | None): Language code (uses active language if None)

Inherits From: KeyTextTransform (Django’s JSON field transform)

Usage:

Can be used in:

• annotate() - Add computed fields

• values() - Select specific values

• order_by() - Sort by localized content

• filter() - Filter by localized values (less common)

• update() - Update with computed values

Example:

from i18n_fields import LocalizedRef

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

# Annotate with specific language
articles = Article.objects.annotate(
    title_en=LocalizedRef('title', 'en'),
    title_es=LocalizedRef('title', 'es')
)

# Use in values()
data = Article.objects.values(
    'id',
    title=LocalizedRef('title', 'en')
)

# Order by localized field
articles = Article.objects.order_by(LocalizedRef('title'))

With Aggregations:

from django.db.models import Count

# Count articles by first letter of English title
from django.db.models.functions import Left

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

With Other Functions:

from django.db.models.functions import Upper, Length

# Convert to uppercase
articles = Article.objects.annotate(
    title_upper=Upper(LocalizedRef('title', 'en'))
)

# Get length
articles = Article.objects.annotate(
    title_length=Length(LocalizedRef('title', 'en'))
)

L()

L(name, lang=None)

Shorthand function for LocalizedRef.

Parameters:

• name (str): The field/column name

• lang (str | None): Language code (uses active language if None)

Returns: LocalizedRef instance

Example:

from i18n_fields import L

# Instead of LocalizedRef('title', 'en')
articles = Article.objects.order_by(L('title', 'en'))

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

# In values()
data = Article.objects.values('id', title=L('title'))

Why Use L()?

• Shorter and more readable

• Commonly used in queries

• Follows Django’s convention (like F() and Q())

Common Use Cases

Ordering

Order by localized fields in different languages:

from i18n_fields import L

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

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

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

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

Annotations

Add computed fields based on localized content:

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

# Add uppercase title
articles = Article.objects.annotate(
    title_upper=Upper(LocalizedRef('title', 'en'))
)

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

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

Values Queries

Extract specific language values:

from i18n_fields import L

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

# Get multiple fields
data = Article.objects.values(
    'id',
    'created_at',
    title=L('title', 'en'),
    content=L('content', 'en')
)

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

Filtering

Filter by localized values (note: direct field lookups are usually better):

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

# Filter by annotation
articles = Article.objects.annotate(
    title_en=LocalizedRef('title', 'en')
).filter(title_en='Hello World')

# Better: use direct lookup
articles = Article.objects.filter(title__en='Hello World')

Aggregations

Aggregate localized data:

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

# Count by category with English titles
stats = Article.objects.annotate(
    title_en=LocalizedRef('title', 'en')
).values('category').annotate(
    count=Count('id')
)

# Average rating by language
from django.db.models import Case, When, IntegerField

avg_rating = Article.objects.annotate(
    rating_en=LocalizedRef('rating', 'en')
).aggregate(
    avg=Avg('rating_en')
)

Advanced Examples

Conditional Expressions

Use with Case/When:

from django.db.models import Case, When, Value, CharField
from i18n_fields import LocalizedRef

# Choose title based on availability
articles = Article.objects.annotate(
    display_title=Case(
        When(
            title__en__isnull=False,
            then=LocalizedRef('title', 'en')
        ),
        When(
            title__es__isnull=False,
            then=LocalizedRef('title', 'es')
        ),
        default=Value('No title'),
        output_field=CharField()
    )
)

Subqueries

Use in subqueries:

from django.db.models import OuterRef, Subquery
from i18n_fields import LocalizedRef

# Get latest article title for each author
latest_articles = Article.objects.filter(
    author=OuterRef('pk')
).order_by('-created_at')

authors = Author.objects.annotate(
    latest_article_title=Subquery(
        latest_articles.values(
            title=LocalizedRef('title', 'en')
        )[:1]
    )
)

Window Functions

Use with window functions (PostgreSQL):

from django.db.models import F, Window
from django.db.models.functions import RowNumber
from i18n_fields import LocalizedRef

# Rank articles by title
articles = Article.objects.annotate(
    title_en=LocalizedRef('title', 'en'),
    rank=Window(
        expression=RowNumber(),
        order_by=F('title_en').asc()
    )
)

Related Fields

Access localized fields through relations:

from i18n_fields import L

# Order by related localized field
comments = Comment.objects.order_by(
    L('article__title', 'en')
)

# Annotate with related localized field
comments = Comment.objects.annotate(
    article_title=LocalizedRef('article__title', 'en')
)

Performance Considerations

Database Indexes

Consider adding indexes for frequently queried languages:

from django.contrib.postgres.indexes import GinIndex

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

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

Query Optimization

from i18n_fields import L

# Good: Single annotation
articles = Article.objects.annotate(
    title_en=L('title', 'en')
).order_by('title_en')

# Less efficient: Multiple queries
articles = Article.objects.all()
sorted_articles = sorted(articles, key=lambda a: a.title.get('en'))

Batch Operations

Use annotations for batch processing:

# Process in batches
articles = Article.objects.annotate(
    title_en=L('title', 'en')
).values('id', 'title_en')

for batch in batch_qs(articles, batch_size=1000):
    process_batch(batch)

Limitations

1. Database-Specific

   Some features require specific databases:

   • GIN indexes: PostgreSQL only

   • Window functions: PostgreSQL, MySQL 8.0+

2. JSON Path Limitations

   Complex JSON path queries may not work:

   # May not work
   articles = Article.objects.filter(
       title__en__in=['Hello', 'World']
   )
   

3. Type Conversions

   Database returns string values for JSON fields. Type conversion happens in Python:

   # Integer comparison may not work as expected
   products = Product.objects.annotate(
       stock_en=L('stock', 'en')
   ).filter(stock_en__gt=10)  # May compare as strings
   

Best Practices

1. Use L() for Readability

   Prefer L() over LocalizedRef for brevity:

   # Good
   articles = Article.objects.order_by(L('title'))
   
   # More verbose
   from i18n_fields import LocalizedRef
   articles = Article.objects.order_by(LocalizedRef('title'))
   

2. Specify Language When Needed

   Be explicit about language for consistent results:

   # Good - explicit
   articles = Article.objects.order_by(L('title', 'en'))
   
   # May vary based on active language
   articles = Article.objects.order_by(L('title'))
   

3. Use Direct Lookups for Filtering

   Prefer field lookups over expressions for filtering:

   # Good
   articles = Article.objects.filter(title__en='Hello')
   
   # Less efficient
   articles = Article.objects.annotate(
       title_en=L('title', 'en')
   ).filter(title_en='Hello')
   

4. Combine with select_related

   Optimize queries with related fields:

   articles = Article.objects.select_related(
       'author', 'category'
   ).annotate(
       title_en=L('title', 'en')
   ).order_by('title_en')
   

See Also

• Advanced Queries - Advanced querying guide

• Fields Reference - Localized field types

• Django Expressions