Filtering

The .filter() method is your primary tool for querying. It translates Python arguments into SQL WHERE clauses.

Basic Syntax

# Exact match (lookup name is implicit)
await Post.objects.filter(active=True)
await Post.objects.filter(author_id=5)

# With a lookup
await Post.objects.filter(views__gt=100)

The pattern is field_name__lookup=value. When no lookup is specified, exact is used.

Comparison Lookups

Post.objects.filter(views__gt=100)    # views > 100
Post.objects.filter(views__gte=100)   # views >= 100
Post.objects.filter(views__lt=50)     # views < 50
Post.objects.filter(views__lte=50)    # views <= 50

String Lookups

Post.objects.filter(title__contains="Python")        # LIKE '%Python%'
Post.objects.filter(title__icontains="python")       # LIKE '%python%' (case-insensitive)
Post.objects.filter(title__startswith="How")          # LIKE 'How%'
Post.objects.filter(title__istartswith="how")         # LIKE 'how%' (case-insensitive)
Post.objects.filter(title__endswith="guide")          # LIKE '%guide'
Post.objects.filter(title__iendswith="Guide")         # LIKE '%guide' (case-insensitive)

Null Checks

Post.objects.filter(body__isnull=True)    # body IS NULL
Post.objects.filter(body__isnull=False)   # body IS NOT NULL

Membership

# IN clause
Post.objects.filter(status__in=["draft", "published"])
Post.objects.filter(id__in=[1, 2, 3, 4, 5])

Range

# BETWEEN clause
Post.objects.filter(views__range=(100, 1000))
Post.objects.filter(created_at__range=(start_date, end_date))

Multiple Filters

Multiple filters are AND-ed together:

# WHERE active = true AND views > 100
Post.objects.filter(active=True, views__gt=100)

Excluding

# WHERE NOT (status = 'draft')
Post.objects.exclude(status="draft")

# Chain filter + exclude
Post.objects.filter(active=True).exclude(title__startswith="Draft")

Built-in Lookups Reference

Lookup	SQL	Example
exact	col = ?	filter(title="Hello")
gt	col > ?	filter(views__gt=100)
gte	col >= ?	filter(views__gte=100)
lt	col < ?	filter(views__lt=50)
lte	col <= ?	filter(views__lte=1000)
contains	col LIKE ?	filter(title__contains="Py")
icontains	LOWER(col) LIKE LOWER(?)	filter(title__icontains="py")
startswith	col LIKE ?	filter(title__startswith="How")
istartswith	LOWER(col) LIKE LOWER(?)	filter(title__istartswith="how")
endswith	col LIKE ?	filter(title__endswith="guide")
iendswith	LOWER(col) LIKE LOWER(?)	filter(title__iendswith="Guide")
isnull	col IS NULL / IS NOT NULL	filter(body__isnull=True)
in	col IN (?, ?, ...)	filter(id__in=[1,2,3])
range	col BETWEEN ? AND ?	filter(views__range=(100,1000))

Date & Time Transforms

These can be chained with other lookups (e.g. created_at__year__gte=2024).

Transform	SQL	Example
date	DATE(col) = ?	filter(created_at__date=date(2024,1,1))
year	EXTRACT(YEAR FROM col) = ? (Postgres) / YEAR(col) = ? (MySQL) / CAST(strftime('%Y', col) AS INTEGER) = ? (SQLite)	filter(created_at__year=2024)
month	EXTRACT(MONTH FROM col) = ? / MONTH(col) = ? / CAST(strftime('%m', col) AS INTEGER) = ?	filter(created_at__month=5)
day	EXTRACT(DAY FROM col) = ? / DAYOFMONTH(col) = ? / CAST(strftime('%d', col) AS INTEGER) = ?	filter(created_at__day=15)
hour	EXTRACT(HOUR FROM col) = ? / HOUR(col) = ? / CAST(strftime('%H', col) AS INTEGER) = ?	filter(created_at__hour=14)
minute	EXTRACT(MINUTE FROM col) = ? / MINUTE(col) = ? / CAST(strftime('%M', col) AS INTEGER) = ?	filter(created_at__minute=30)
second	EXTRACT(SECOND FROM col) = ? / SECOND(col) = ? / CAST(strftime('%S', col) AS INTEGER) = ?	filter(created_at__second=45)
week	EXTRACT(WEEK FROM col) = ? / WEEK(col) = ? / CAST(strftime('%W', col) AS INTEGER) = ?	filter(created_at__week=20)
dow	EXTRACT(DOW FROM col) = ? / DAYOFWEEK(col) = ? / CAST(strftime('%w', col) AS INTEGER) = ?	filter(created_at__dow=1)
quarter	EXTRACT(QUARTER FROM col) = ? / QUARTER(col) = ? / ((CAST(strftime('%m', col) AS INTEGER) + 2) / 3) = ?	filter(created_at__quarter=2)
time	TIME(col) = ? (Postgres/MySQL) / time(col) = ? (SQLite)	filter(created_at__time='12:34:56')
iso_week	EXTRACT(ISOWEEK FROM col) = ? / complex MySQL expression / CAST(strftime('%W', col) AS INTEGER) = ? (SQLite)	filter(created_at__iso_week=15)
iso_dow	EXTRACT(ISODOW FROM col) = ? / ((DAYOFWEEK(col) + 5) % 7) + 1 = ? (MySQL) / CAST(strftime('%w', col) AS INTEGER) = ? (SQLite)	filter(created_at__iso_dow=3)

JSON Transforms & Lookups

These work on JSONB/JSON fields and can be chained (e.g. metadata__key__priority__exact="high").

Transform/Lookup	SQL (Postgres)	Example
key	(col->>'key')	filter(metadata__key__priority__exact="high")
key_text	(col->>'key')::text	filter(metadata__key_text__priority__icontains="urgent")
json	(col::jsonb)	filter(metadata__json__contains={'active': true})
has_key	col ? 'key'	filter(metadata__has_key="priority")
has_keys	col ?& array['key1','key2']	filter(metadata__has_keys=["priority","status"])
contains	col @> ?	filter(metadata__contains={"priority": "high"})
contained_by	col <@ ?	filter(metadata__contained_by={"priority": "high"})

Custom Lookups

Register your own SQL lookups:

import ryx

# Postgres ILIKE
ryx.register_lookup("ilike", "{col} ILIKE ?")

# Usage
Post.objects.filter(title__ilike="%python%")

# Decorator style
@ryx.lookup("uuid_prefix")
def uuid_prefix_lookup(field, value):
    """{col}::text LIKE ?"""

Next Steps

→ Q Objects — OR and NOT expressions → Custom Lookups — Advanced lookup patterns