Lookup reference
Lookups are written as field__lookup keys in filter(), exclude() and get(). A key with
no lookup means exact. See Making queries for the QuerySet API.
await Post.objects.filter({ title__icontains: 'workers', createdAt__year: 2026, author__username__in: ['rifat', 'ana'],})Field lookups
Section titled “Field lookups”? is a bound parameter ($1, $2, … on Postgres) and col is the column. Pattern lookups
escape their input (%, _ and \ for LIKE; *, ? and [ for GLOB), so user input is
always matched literally.
| Lookup | Example | SQLite / D1 | Postgres |
|---|---|---|---|
exact |
{ slug: 'hello' } |
col = ? |
col = ? |
exact with null |
{ publishedAt: null } |
col IS NULL |
col IS NULL |
iexact |
{ username__iexact: 'Rifat' } |
UPPER(col) = UPPER(?) |
UPPER(col) = UPPER(?) |
contains |
{ title__contains: 'Hono' } |
col GLOB '*Hono*' |
col LIKE '%Hono%' ESCAPE '\' |
icontains |
{ title__icontains: 'hono' } |
col LIKE '%hono%' ESCAPE '\' |
col ILIKE '%hono%' ESCAPE '\' |
startswith |
{ title__startswith: 'How' } |
col GLOB 'How*' |
col LIKE 'How%' ESCAPE '\' |
istartswith |
{ title__istartswith: 'how' } |
col LIKE 'how%' ESCAPE '\' |
col ILIKE 'how%' ESCAPE '\' |
endswith |
{ email__endswith: '@example.com' } |
col GLOB '*@example.com' |
col LIKE '%@example.com' ESCAPE '\' |
iendswith |
{ email__iendswith: '@EXAMPLE.com' } |
col LIKE '%@EXAMPLE.com' ESCAPE '\' |
col ILIKE '%@EXAMPLE.com' ESCAPE '\' |
in |
{ id__in: [1, 2, 3] } |
col IN (?, ?, ?) |
col IN (?, ?, ?) |
gt |
{ views__gt: 100 } |
col > ? |
col > ? |
gte |
{ views__gte: 100 } |
col >= ? |
col >= ? |
lt |
{ createdAt__lt: cutoff } |
col < ? |
col < ? |
lte |
{ createdAt__lte: cutoff } |
col <= ? |
col <= ? |
range |
{ createdAt__range: [start, end] } |
col BETWEEN ? AND ? |
col BETWEEN ? AND ? |
isnull |
{ publishedAt__isnull: true } |
col IS NULL / IS NOT NULL |
col IS NULL / IS NOT NULL |
regex |
{ slug__regex: '^v[0-9]+' } |
col REGEXP ? (not on D1) |
col ~ ? |
iregex |
{ slug__iregex: '^V[0-9]+' } |
col REGEXP '(?i)' + pattern (not on D1) |
col ~* ? |
The pattern in the contains family is passed as a parameter; the table shows its value
inline for readability. On Postgres, non-text columns are cast to text for text lookups.
Notes:
- Case sensitivity is the same everywhere.
contains,startswithandendswithare case-sensitive on every backend (SQLite and D1 useGLOB, because SQLite’sLIKEignores ASCII case), and thei...variants are case-insensitive. SQLite’sLIKEonly folds ASCII letters, soicontainson non-ASCII text can still differ from Postgres. nullvalues:exact/iexactwithnullcompile toIS NULL;containsand friends withnullmatch nothing.inwith an empty array compiles to a condition that matches nothing (1 = 0).inwith many values on SQLite/D1: above 50 values the list is sent as one JSON parameter (col IN (SELECT value FROM json_each(?))), which keeps queries under D1’s limit of 100 bound parameters per statement.inwith a QuerySet compiles to a subquery:filter({ author__in: User.objects.filter({ isStaff: true }) }).rangeis inclusive on both ends, like SQLBETWEEN. For datetimes,range: [start, end]withendat midnight excludes the rest of that day.- Values can be expressions:
{ updatedAt__gt: F('createdAt') },{ title__icontains: F('slug') }.
Transforms
Section titled “Transforms”Transforms go between the field and the lookup, and can be chained with any lookup:
createdAt__year__gte: 2025, createdAt__date: '2026-11-12'. Without a lookup, exact is
implied. DateTimeField values are compared in UTC on both dialects.
| Transform | Value | SQLite / D1 | Postgres |
|---|---|---|---|
date |
'YYYY-MM-DD' |
substr(col, 1, 10) |
CAST((col AT TIME ZONE 'UTC') AS date) |
time |
'HH:MM:SS' |
substr(col, 12, 8) |
CAST((col AT TIME ZONE 'UTC') AS time) |
year |
number | CAST(strftime('%Y', col) AS integer) |
CAST(EXTRACT(YEAR FROM (col AT TIME ZONE 'UTC')) AS integer) |
month |
1-12 | strftime('%m', ...) |
EXTRACT(MONTH ...) |
day |
1-31 | strftime('%d', ...) |
EXTRACT(DAY ...) |
hour, minute, second |
number | strftime('%H' / '%M' / '%S', ...) |
EXTRACT(HOUR / MINUTE / SECOND ...) |
week_day |
1 (Sunday) to 7 (Saturday), as in Django | strftime('%w', ...) + 1 |
EXTRACT(DOW ...) + 1 |
quarter |
1-4 | computed from the month | EXTRACT(QUARTER ...) |
Transforms wrap the column in a function, so they cannot use a plain index on it. For
large tables, prefer a range on the column itself: { createdAt__gte: start, createdAt__lt: end }.
Spanning relations
Section titled “Spanning relations”Follow foreign keys, reverse foreign keys and many-to-many relations with __, then apply a
lookup on the final field:
// forward FK: posts whose author's username is 'rifat'Post.objects.filter({ author__username: 'rifat' })
// reverse FK (uses relatedQueryName, or relatedName, or the lower-camel model name):// users with a published postUser.objects.filter({ posts__published: true })
// many-to-many: posts tagged 'workers'Post.objects.filter({ tags__name: 'workers' })
// several hopsComment.objects.filter({ post__author__email__iendswith: '@example.com' })Spanning a multi-valued relation (reverse FK or M2M) compiles to an EXISTS (...) subquery
rather than a join, so a post with two matching tags still appears once and .distinct() is
rarely needed. Forward foreign keys and one-to-ones are joined. Without relatedName, the
reverse lookup name is the lower-camel model name: User.objects.filter({ post__title: 'Hi' }).
As in Django, conditions in one filter() call apply to the same related object, while
chained filter() calls can match different related objects:
// posts with a single tag that is both named 'workers' and featuredPost.objects.filter({ tags__name: 'workers', tags__featured: true })
// posts with some tag named 'workers' and some (possibly other) featured tagPost.objects.filter({ tags__name: 'workers' }).filter({ tags__featured: true })Filtering on the relation itself compares primary keys. Both forms work:
Post.objects.filter({ author: user }) // a model instancePost.objects.filter({ author: 1 }) // a primary keyPost.objects.filter({ authorId: 1 }) // the attname, no joinQ objects
Section titled “Q objects”Combine conditions with or, and and Q.not:
import { Q } from '@ghuri/orm'
Post.objects.filter( Q({ title__icontains: 'd1' }).or(Q({ title__icontains: 'postgres' })).and(Q.not({ published: false })),)| Expression | SQL shape |
|---|---|
Q({ a: 1, b: 2 }) |
a = 1 AND b = 2 |
Q({ a: 1 }).or(Q({ b: 2 })) |
(a = 1 OR b = 2) |
Q({ a: 1 }).and(Q({ b: 2 })) |
(a = 1 AND b = 2) |
Q.not({ a: 1 }) |
NOT (a = 1) |
Q.and(a, b, ...) and Q.or(a, b, ...) combine any number of conditions, and q.not()
negates an existing Q. exclude({ … }) is filter(Q.not({ … })).
F expressions
Section titled “F expressions”F('field') refers to a column of the same row, so the database does the work:
import { F } from '@ghuri/orm'
// increment without a read-modify-write raceawait Post.objects.filter({ id: 1 }).update({ views: F('views').add(1) })
// compare two columnsPost.objects.filter({ updatedAt__gt: F('createdAt') })| Method | SQL |
|---|---|
F('a').add(x) |
a + x |
F('a').sub(x) |
a - x |
F('a').mul(x) |
a * x |
F('a').div(x) |
a / x |
F('a').mod(x) |
a % x |
F('a').asc() / .desc({ nullsLast: true }) |
ordering, with NULL placement |
F() also spans relations: F('author__id').
Functions and other expressions
Section titled “Functions and other expressions”Exported from @ghuri/orm and usable in annotate(), filter() values, orderBy() and
update():
| Expression | SQL |
|---|---|
Value(x) |
? (a bound value) |
Lower(e), Upper(e) |
LOWER(e), UPPER(e) |
Length(e) |
LENGTH(e) (a number) |
Coalesce(a, b, ...) |
COALESCE(a, b, ...) |
Concat(a, b, ...) |
(COALESCE(a, '') || COALESCE(b, '') ...): NULL parts become '' |
Now() |
CURRENT_TIMESTAMP on Postgres, an ISO-8601 UTC timestamp on SQLite |
new Func('NAME', [args], outputField?) |
NAME(args) |
new RawSQL('sql with ?', [params], outputField?) |
the SQL as written (not portable) |
String arguments are field references (Lower('title')); wrap literals in Value().
Aggregates
Section titled “Aggregates”Use with aggregate() (one result for the whole QuerySet) or annotate() (one value per row):
import { Count, Sum, Avg, Min, Max } from '@ghuri/orm'
await Post.objects.aggregate({ total: Count('id'), avgViews: Avg('views') })// → { total: 42, avgViews: 118.5 }
await User.objects.annotate({ numPosts: Count('posts') }).filter({ numPosts__gt: 2 })| Aggregate | SQL | Result type | Empty set |
|---|---|---|---|
Count(field = '*', { distinct?, filter? }) |
COUNT(col) |
number |
0 |
Sum(field, { distinct?, filter? }) |
SUM(col) |
number (string for DecimalField) |
null |
Avg(field, ...) |
AVG(col) |
number (string for DecimalField) |
null |
Min(field, ...) |
MIN(col) |
field type | null |
Max(field, ...) |
MAX(col) |
field type | null |
filter is a Q or a filter object, relative to the queried model:
Count('comments', { filter: Q({ comments__approved: true }) }). Annotations can be used in
later filter(), exclude() and orderBy() calls; filtering on an aggregate annotation
compiles to HAVING. Aggregates over reverse or many-to-many relations in annotate() compile
to correlated subqueries, so several of them can be combined without multiplying rows.
Ordering
Section titled “Ordering”orderBy('-createdAt', 'title'): a leading - means descending. '?' orders randomly
(RANDOM() on both dialects). Related fields span with __: orderBy('author__username').
Expressions work too: orderBy(Lower('title')), orderBy(F('rating').desc({ nullsLast: true })).
Without orderBy(), Meta.ordering applies. NULLs sort first in ascending order on every
backend, so results do not change order when you move between SQLite and Postgres.