python / expert
Snippet
Custom Lookup subclass for Postgres Array Overlap Queries
Django's ORM can be extended with custom lookup operators by subclassing Lookup and implementing `as_sql`. Registering `ArrayOverlapLookup` allows using the PostgreSQL overlap operator `&&` directly in QuerySet filters for array fields.
snippet.py
python
1
2
3
4
5
6
7
8
9
10
11
12
from django.db.models import Lookupfrom django.db.models.fields import Field@Field.register_lookupclass ArrayOverlapLookup(Lookup):lookup_name = 'overlap'def as_sql(self, compiler, connection):lhs, lhs_params = self.process_lhs(compiler, connection)rhs, rhs_params = self.process_rhs(compiler, connection)params = lhs_params + rhs_paramsreturn f"{lhs} && {rhs}", params
django
Breakdown
1
@Field.register_lookup
Registers the custom lookup class globally on Django Model Field objects.
2
class ArrayOverlapLookup(Lookup):
Inherits from Django base Lookup to encapsulate SQL translation for field comparisons.
3
lookup_name = 'overlap'
Defines the string keyword used in field lookups (e.g., tags__overlap=[1, 2]).
4
def as_sql(self, compiler, connection):
Overrides SQL compilation method to return native database vendor syntax and parameter tuples.
5
lhs, lhs_params = self.process_lhs(compiler, connection)
Compiles left-hand side expression into SQL string and positional database parameters.
6
rhs, rhs_params = self.process_rhs(compiler, connection)
Compiles right-hand side argument into SQL representation and parameters.
7
return f"{lhs} && {rhs}", params
Combines compiled expressions into PostgreSQL array overlap syntax with parameter bindings.