Django-Translatable-Fields/django_translatable_fields/search.py

# -*- coding: utf-8 -*-
"""
Django Translatable Fields - Advanced Search Functionality

This module provides database-optimized search capabilities for translatable
fields. It includes database-specific implementations for PostgreSQL, MySQL,
and SQLite to ensure optimal performance when searching JSON-stored translations.

Key Features:
- Database-specific JSON search optimizations
- PostgreSQL: Native JSON operators (->>, ->, etc.)
- MySQL: JSON_EXTRACT and JSON_UNQUOTE functions
- SQLite: json_extract function
- Generic fallback for other databases
- Case-insensitive search support
- Multi-field search across different translatable fields

The module automatically detects the database backend and uses the most
efficient search method available. This ensures fast search performance
even with large datasets containing complex translation structures.

Usage:
    # Used internally by TranslatableQuerySet
    results = TranslatableSearch.search_translatable_field(
        queryset, 'name', 'search_term', 'de'
    )

Author: Holger Sielaff <holger@backender.de>
Version: 0.1.0
"""

import json
from django.db import models, connection
from django.db.models import Q
from django.utils.translation import get_language


class TranslatableSearch:
    """
    Advanced search functionality for translatable fields with database-specific optimizations.
    
    This class provides database-optimized search methods for translatable fields.
    PostgreSQL is strongly recommended for optimal performance as it provides
    native JSON operators that are significantly faster than generic approaches.
    
    Performance Comparison:
    - PostgreSQL: Uses native ->, ->> operators (~10x faster)
    - MySQL: Uses JSON_EXTRACT functions (good performance)
    - SQLite: Uses json_extract function (acceptable for small datasets)
    - Others: Generic fallback (slower performance)
    
    For production use with large datasets, PostgreSQL is highly recommended.
    """
    
    @staticmethod
    def get_database_vendor():
        """Get the database vendor (postgresql, mysql, sqlite, etc.)"""
        return connection.vendor
    
    @classmethod
    def search_translatable_field(cls, queryset, field_name, query, language=None):
        """
        Search in a translatable field for the specified language with database-specific optimizations.
        
        Args:
            queryset: QuerySet to filter
            field_name: Name of the translatable field to search in
            query: Search query string  
            language: Language code to search (defaults to current language)
        
        Returns:
            Filtered QuerySet
        """
        if not query:
            return queryset
            
        language = language or get_language() or 'en_US'
        query = str(query).strip()
        
        vendor = cls.get_database_vendor()
        
        if vendor == 'postgresql':
            return cls._search_postgresql(queryset, field_name, query, language)
        elif vendor == 'mysql':
            return cls._search_mysql(queryset, field_name, query, language)
        elif vendor == 'sqlite':
            return cls._search_sqlite(queryset, field_name, query, language)
        else:
            # Generic fallback
            return cls._search_generic(queryset, field_name, query, language)
    
    @classmethod
    def _search_postgresql(cls, queryset, field_name, query, language):
        """PostgreSQL-specific JSON search using -> operator"""
        # Use PostgreSQL JSON operators for efficient search
        return queryset.extra(
            where=[f"LOWER({field_name}->>'%s') LIKE LOWER(%s)"],
            params=[language, f'%{query}%']
        )
    
    @classmethod  
    def _search_mysql(cls, queryset, field_name, query, language):
        """MySQL-specific JSON search using JSON_UNQUOTE and JSON_EXTRACT"""
        return queryset.extra(
            where=["LOWER(JSON_UNQUOTE(JSON_EXTRACT(%s, CONCAT('$.', %%s)))) LIKE LOWER(%%s)" % field_name],
            params=[language, f'%{query}%']  
        )
    
    @classmethod
    def _search_sqlite(cls, queryset, field_name, query, language):
        """SQLite JSON search using json_extract"""
        return queryset.extra(
            where=[f"LOWER(json_extract({field_name}, '$.{language}')) LIKE LOWER(%s)"],
            params=[f'%{query}%']
        )
    
    @classmethod
    def _search_generic(cls, queryset, field_name, query, language):
        """Generic fallback using text search in JSON"""
        # Fallback: search for the pattern in the JSON text
        search_pattern = f'"{language}":"%{query}%"'
        return queryset.filter(**{f'{field_name}__icontains': search_pattern})
    
    @classmethod
    def search_multiple_fields(cls, queryset, query, language=None, fields=None):
        """
        Search across multiple translatable fields.
        
        Args:
            queryset: QuerySet to filter
            query: Search query string
            language: Language code to search (defaults to current language) 
            fields: List of field names to search (auto-detected if None)
        
        Returns:
            Filtered QuerySet
        """
        if not query:
            return queryset
            
        language = language or get_language() or 'en_US'
        
        # Auto-detect translatable fields if not specified
        if fields is None:
            fields = cls._get_translatable_fields(queryset.model)
        
        if not fields:
            return queryset
        
        # Build OR query across all fields
        q_objects = Q()
        for field_name in fields:
            # Create a sub-queryset for this field and extract the WHERE clause
            field_qs = cls.search_translatable_field(
                queryset.model.objects.all(), 
                field_name, 
                query, 
                language
            )
            
            # Add to OR conditions - this is a simplified approach
            # In practice, you might want to use more sophisticated query building
            q_objects |= Q(**{f'{field_name}__icontains': f'"{language}":'}) & Q(**{f'{field_name}__icontains': query})
        
        return queryset.filter(q_objects)
    
    @classmethod
    def _get_translatable_fields(cls, model):
        """Get list of translatable field names for a model"""
        translatable_fields = []
        for field in model._meta.get_fields():
            if hasattr(field, 'translatable') and field.translatable:
                translatable_fields.append(field.name)
        return translatable_fields


def search_products(query, language=None, fields=None):
    """
    Convenience function to search products.
    
    Args:
        query: Search query string
        language: Language code (defaults to current language)
        fields: List of field names to search (defaults to ['name', 'description'])
    
    Returns:
        QuerySet of matching products
    
    Example:
        products = search_products('moep', 'de')
        products = search_products('hello', fields=['name'])
    """
    from .models import Product  # Adjust import as needed
    
    queryset = Product.objects.all()
    fields = fields or ['name', 'description']
    
    return TranslatableSearch.search_multiple_fields(
        queryset, query, language, fields
    )


class SearchForm:
    """
    Helper class to create search forms with language selection
    """
    
    @staticmethod
    def get_language_choices():
        """Get available language choices from Django settings"""
        from django.conf import settings
        return getattr(settings, 'LANGUAGES', [('en', 'English'), ('de', 'German')])
    
    @classmethod
    def create_search_context(cls, request):
        """
        Create context for search forms.
        
        Args:
            request: Django request object
            
        Returns:
            Dictionary with search context
        """
        query = request.GET.get('q', '')
        language = request.GET.get('lang', get_language() or 'en_US')
        fields = request.GET.getlist('fields')
        
        return {
            'search_query': query,
            'search_language': language,
            'search_fields': fields,
            'available_languages': cls.get_language_choices(),
            'current_language': get_language() or 'en_US'
        }