Field Lookups¶
Field lookups are keyword arguments passed to filter(), exclude(), get(), and Q(). The syntax is field__lookup=value.
Person.objects.filter(age__lt=18)
# ^^^ ^^
# field lookup
If no lookup is specified, eq (exact match) is used by default:
Person.objects.filter(name='John')
# equivalent to:
Person.objects.filter(name__eq='John')
Comparison Lookups¶
| Lookup | SQL | Example |
|---|---|---|
eq |
= |
age__eq=18 |
neq |
!= |
gender__neq='male' |
gt |
> |
age__gt=18 |
gte |
>= |
age__gte=18 |
lt |
< |
age__lt=18 |
lte |
<= |
age__lte=18 |
in |
IN |
age__in=[20, 30, 40] (foreign keys: see Filtering by Foreign Keys) |
isnull |
IS NULL / IS NOT NULL |
email__isnull=True |
regex |
regex match | name__regex=r'^J.*n$' |
iregex |
case-insensitive regex match | name__iregex=r'^j.*n$' |
String Lookups¶
| Lookup | Case-sensitive | Description |
|---|---|---|
contains |
Yes | name__contains='har' — matches "Charles", not "Harry" |
icontains |
No | name__icontains='har' — matches "Charles" and "Harry" |
startswith |
Yes | name__startswith='Jo' |
istartswith |
No | name__istartswith='jo' |
endswith |
Yes | name__endswith='son' |
iendswith |
No | name__iendswith='SON' |
Nested Field Lookups¶
A trailing segment that doesn't match a known lookup is kept as part of the field path with an implicit eq. For JSON / structured columns this works as a nested-field lookup; for scalar columns the backend will reject the unknown path.
Person.objects.filter(json_field__nested_field__eq='value').execute()
await Person.objects.filter(json_field__nested_field__eq='value').aexecute()
Filtering by Related Object Fields¶
Use select_related first, then filter by related fields using __ notation:
Person.objects.select_related('company').filter(company__name='Acme').execute()
await Person.objects.select_related('company').filter(company__name='Acme').aexecute()
This works with multiple levels of nesting:
Person.objects.select_related(
'company',
'company__location',
).filter(company__location__name='New York')
Tip
For filtering by FK identity alone, prefer the FK forms in
Filtering by Foreign Keys — they don't
require select_related.
Optimizing reverse-FK / M2M reads with prefetch_related¶
select_related performs a SQL JOIN — best for forward FK and used at filter time. For reverse-FK and M2M, prefer prefetch_related: it runs one extra SELECT per relation and attaches the rows to the parent instances, avoiding N+1.
# One SELECT for authors + one SELECT for all books for those authors
authors = Author.objects.prefetch_related('book_set').execute()
for author in authors:
print(author.book_set) # no extra query
# Filter what gets prefetched with a Prefetch object
from amsdal_models.querysets.prefetch import Prefetch
authors = Author.objects.prefetch_related(
Prefetch('book_set', queryset=Book.objects.filter(year__gte=2020)),
).execute()
Filtering with prefetch_related happens inside the inner queryset — it does not affect the outer WHERE.
Metadata Lookups¶
Use the _metadata prefix to filter by Metadata fields:
| Field | Type | Description |
|---|---|---|
is_deleted |
bool |
Whether the object is soft-deleted |
created_at |
int |
Creation timestamp (ms since epoch) |
updated_at |
int |
Last update timestamp (ms since epoch) |
object_id |
Any |
Object identifier |
object_version |
str \| Versions |
Object version |
prior_version |
str \| None |
Previous version identifier |
Note
created_at and updated_at are stored as int (milliseconds since epoch). Pass int(dt.timestamp() * 1000) — there is no automatic datetime → int coercion in the filter layer. Passing a datetime or ISO string silently matches zero rows.
# Deleted records
Person.objects.filter(_metadata__is_deleted=True)
Warning
_metadata__* filters apply only on historical / lakehouse connections. On state connections they are silently dropped (no error, no effect). To filter by metadata on state, query the lakehouse explicitly: Person.objects.using('lakehouse').filter(_metadata__is_deleted=True).
from datetime import datetime, timedelta, timezone
# Count records created in the last 24 hours.
# created_at is int ms — convert datetime to ms-since-epoch explicitly.
threshold_ms = int((datetime.now(timezone.utc) - timedelta(hours=24)).timestamp() * 1000)
result = Person.objects.filter(
_metadata__created_at__gt=threshold_ms,
).count().execute()
from datetime import datetime, timedelta, timezone
threshold_ms = int((datetime.now(timezone.utc) - timedelta(hours=24)).timestamp() * 1000)
result = await Person.objects.filter(
_metadata__created_at__gt=threshold_ms,
).count().aexecute()
Address Lookups¶
Use the _address prefix to filter by object identity and version:
_address__object_id— unique object identifier (works in both state and historical connections)_address__object_version— specific version orVersions.ALL/Versions.LATEST(historical only)_address__class_version— schema version (historical only)
Note
_address__object_version and _address__class_version require a
historical connection (including lakehouse). _address__object_id works
in both connection types — in state it resolves directly to the model's
primary-key column(s).
For composite primary keys, pass a tuple/list in PRIMARY_KEY order:
Person.objects.filter(_address__object_id=('alice', 'doe'))
Person.objects.filter(_address__object_id__in=[('alice', 'doe'), ('bob', 'roe')])
from amsdal import Versions
# Get all versions of a specific object
result = Person.objects.filter(
_address__object_id='12345',
_address__object_version=Versions.ALL,
_address__class_version=Versions.ALL,
).execute()
from amsdal import Versions
result = await Person.objects.filter(
_address__object_id='12345',
_address__object_version=Versions.ALL,
_address__class_version=Versions.ALL,
).aexecute()
Filtering by Foreign Keys¶
A foreign-key value can be passed in any of the following forms:
| Form | Example |
|---|---|
| Model instance | filter(location=new_york) |
Reference object |
filter(location=new_york.build_reference()) |
| Serialized Reference string | filter(location=ref.ref.to_string()) |
None |
filter(location=None) — matches rows with no FK set |
All forms work in both state and historical (lakehouse) connections.
new_york = Location.objects.get(name='New York').execute()
# by model instance
Person.objects.filter(location=new_york).execute()
# by Reference
ref = new_york.build_reference()
Person.objects.filter(location=ref).execute()
# by serialized Reference string
Person.objects.filter(location=ref.ref.to_string()).execute()
# IS NULL
Person.objects.filter(location=None).execute()
new_york = await Location.objects.get(name='New York').aexecute()
ref = await new_york.abuild_reference()
await Person.objects.filter(location=new_york).aexecute()
await Person.objects.filter(location=ref).aexecute()
await Person.objects.filter(location=ref.ref.to_string()).aexecute()
await Person.objects.filter(location=None).aexecute()
__in for Foreign Keys¶
Pass a list of FK values to match any of them. Elements may be a mix of
Reference, model instances, and serialized Reference strings:
refs = [
new_york.build_reference(),
london, # model instance
san_francisco.build_reference().ref.to_string(), # serialized
]
Person.objects.filter(location__in=refs).execute()
await Person.objects.filter(location__in=refs).aexecute()
None is not allowed inside __in lists — combine with a separate
condition for IS NULL:
from amsdal.queryset import Q
Person.objects.filter(Q(location__in=refs) | Q(location=None))
Composite Primary Keys¶
If the referenced model has a composite primary key, the same forms apply. Reference / model-instance forms work as-is. The serialized-string form encodes the composite as a list internally — nothing extra is needed in user code.
Filtering by Reverse-FK Relationships¶
Note
On lakehouse, the instance accessor (post.tags.filter(...)) and the root filter (Post.objects.filter(tags=t)) use different defaults — accessor filters "current active" by default, root filters "raw has-or-had". See Relationships — Lakehouse historical semantics.
You can filter a model by attributes of its reverse-FK related rows using <related_name>__<field> notation — no select_related / prefetch_related required. The query planner emits an EXISTS subquery / JOIN automatically.
# Authors who wrote at least one book titled "Dune"
Author.objects.filter(book_set__title='Dune').execute()
# Combined with field lookups on the related side
Author.objects.filter(book_set__year__gte=2020).execute()
# Authors with NO books at all
Author.objects.filter(book_set__isnull=True).execute()
# Authors with at least one book
Author.objects.filter(book_set__isnull=False).execute()
await Author.objects.filter(book_set__title='Dune').aexecute()
Negation uses a correlated NOT EXISTS:
# Authors who have NO books titled "Dune"
Author.objects.exclude(book_set__title='Dune').execute()
# Equivalent with Q:
Author.objects.filter(~Q(book_set__title='Dune')).execute()
Filtering by Many-to-Many Relationships¶
Same syntax — <m2m_field>__<field> — and the same EXISTS planning, but over the through-table.
# Posts tagged with "python"
Post.objects.filter(tags__name='python').execute()
# Membership: posts tagged with any of these tags
Post.objects.filter(tags__in=[python_tag, django_tag]).execute()
# Posts that have NO tags at all
Post.objects.filter(tags__isnull=True).execute()
# Posts that have at least one tag
Post.objects.filter(tags__isnull=False).execute()
# Posts NOT tagged with "python"
Post.objects.exclude(tags__name='python').execute()
await Post.objects.filter(tags__name='python').aexecute()
Note
M2M tags__in=[...] accepts model instances, References, or serialized Reference strings (same forms as forward-FK __in). M2M tags=None is equivalent to tags__isnull=True.
_address__object_id on a Foreign Key — PK-only Filtering¶
When you only know the primary key of the referenced object (not a full
Reference, and don't need the version), use <fk>___address__object_id to
filter by FK PK directly. This works without select_related.
# simple PK
Person.objects.filter(location___address__object_id='nyc').execute()
Person.objects.filter(location___address__object_id__in=['nyc', 'ldn']).execute()
# composite PK — pass a tuple/list, in PRIMARY_KEY order
Person.objects.filter(location___address__object_id=('nyc', 'office')).execute()
Person.objects.filter(
location___address__object_id__in=[('nyc', 'office'), ('ldn', 'home')],
).execute()
await Person.objects.filter(location___address__object_id='nyc').aexecute()
await Person.objects.filter(
location___address__object_id__in=[('nyc', 'office'), ('ldn', 'home')],
).aexecute()
Note
PK-only filtering does not apply any object_version constraint.
Pass a Reference (e.g. filter(location=frozen_ref)) when version-aware
matching is required — frozen References match the exact recorded
object_version, while LATEST References match the latest version.
Per-Hop Version Overrides (historical)¶
When querying through a relation chain in a historical / lakehouse connection, you can override version-scope on a specific hop using triple-underscore directives. These are stripped from the predicate at planning time and applied to the corresponding JOIN — they don't behave as ordinary value filters.
| Directive (per hop) | Meaning |
|---|---|
<hop>___address__object_version=<v> |
Pin this hop to a specific object_version |
<hop>___address__object_version=Versions.LATEST |
Resolve this hop against the latest version |
<hop>___address__class_version=<v> |
Pin this hop's class to a specific schema version |
<hop>___metadata__is_deleted=True/False |
Include / exclude soft-deleted rows on this hop |
from amsdal import Versions
# Filter authors whose latest book version has title='Dune', regardless of
# what version of the FK the parent points to.
Author.objects.using('lakehouse').filter(
book_set___address__object_version=Versions.LATEST,
book_set__title='Dune',
).execute()
# Include soft-deleted books in the match
Author.objects.using('lakehouse').filter(
book_set___metadata__is_deleted=True,
book_set__title='Dune',
).execute()
Warning
Only is_deleted is recognized in the ___metadata__ per-hop form; other metadata fields (created_at, updated_at, …) are not.
Warning
<hop>___address__class_version=Versions.ALL raises ValueError — the planner cannot collapse "all class versions" on a single hop. The root-level form (_address__class_version=Versions.ALL without a hop prefix) is allowed.
If you need to query across all class versions of a related model, query the through-model directly (M2M) or the target model directly (reverse-FK):
from amsdal_utils.models.enums import Versions
# M2M — via through
Post.tags_through.objects.using('lakehouse').filter(
_address__class_version=Versions.ALL,
).execute()
# Reverse-FK — via the target model directly
Book.objects.using('lakehouse').filter(
author=author,
_address__class_version=Versions.ALL,
).execute()