Making queries
A query is built from adorm's own building blocks (Q, F, lookups, functions, aggregates, other expressions) into an immutable Query. Operations read and write with it, and a QuerySet runs them on a session. Building a query opens no connection, imports no ORM and needs no model class. A driver translates the query into its target's constructs (Django lookups and expressions, SQL, SQLAlchemy Core). The Django driver and the native PostgreSQL driver execute every construct on this page.
from adorm import Count, F, Lower, Q
from adorm.query import Query
members = (
Query("club.Member")
.filter(Q(name__icontains="ann") | Q(nick__icontains="ann"), active=True)
.exclude(club__isnull=True)
.annotate(payments=Count("payment"))
.order_by(Lower("name"), "-joined")
)[:20]A model is named by its label, "app_label.ModelName", and fields by their adorm names. The driver resolves both when it executes the query.
Immutability
Every building block is immutable. A method of Query returns a new query and leaves the original unchanged; a query can be stored at module level, shared between tasks and refined independently:
active = Query("club.Member").filter(active=True)
adults = active.filter(age__gte=18)
minors = active.filter(age__lt=18)
assert active == Query("club.Member").filter(active=True) # unchangedNodes compare by value and are hashable:
cache = {adults: "adults"}
assert cache[Query("club.Member").filter(active=True).filter(age__gte=18)] == "adults"Setting an attribute of a node raises AttributeError. Iterables passed to in, range and overlap are copied into tuples; changing the original list does not change the query.
Field lookups
A keyword argument of filter(), exclude() or Q is a field lookup: a field path, __, and a lookup name. Without a lookup name the lookup is exact.
Query("club.Member").filter(name="Ann") # exact
Query("club.Member").filter(name__iexact="ann")
Query("club.Member").filter(age__gte=18, age__lt=65)
Query("club.Member").filter(name__startswith="A")
Query("club.Member").filter(email__icontains="@example.org")
Query("club.Member").filter(code__regex=r"^[A-Z]{3}-\d+$")
Query("club.Member").filter(pk__in=[1, 2, 3])
Query("club.Member").filter(joined__range=(start, end))
Query("club.Member").filter(club__isnull=True)
Query("club.Club").filter(disciplines__overlap=["Lead", "Dinghy"]) # an array field sharing an itemname=None and name__iexact=None become name__isnull=True; any other lookup with None raises ValueError. The lookup reference lists every lookup.
Relation traversal
A field path crosses relations with __, as in Django. The last segment is the lookup when it names a registered lookup; otherwise the whole key is the field path:
Query("club.Member").filter(club__name="Chess") # club → name, exact
Query("club.Member").filter(club__owner__email__iendswith=".si")
Query("club.Club").filter(member__age__lt=18) # reverse relationA field whose name equals a lookup name takes an explicit lookup: filter(range__exact=5).
Conditions on a multi-valued relation (a reverse foreign key, a many-to-many relation) in one filter() call apply to the same related row; conditions in separate calls may each match a different one, as in Django:
Query("club.Club").filter(member__age__lt=18, member__guest=True) # a member who is a minor guest
Query("club.Club").filter(member__age__lt=18).filter(member__guest=True) # a minor member and a guest memberQueries as subqueries
in and overlap take a query instead of a list; the query becomes a subquery. It selects one column with values(), or the primary key when it selects none:
active_clubs = Query("club.Club").filter(active=True)
Query("club.Member").filter(club__in=active_clubs) # club in (select id ...)
Query("club.Member").filter(email__in=Query("club.Invite").values("email"))
Query("club.Club").filter(disciplines__overlap=Query("club.Event").values("discipline")) # && array(select ...)A query passed to any other lookup also becomes a Subquery; it must return a single row.
Combining conditions with Q
Q holds lookups joined with AND. & (AND), | (OR) and ~ (NOT) combine them:
from adorm import Q
Q(name__startswith="A") | Q(name__startswith="B")
Q(active=True) & ~Q(club__name="Chess")
Query("club.Member").filter(Q(age__lt=18) | Q(guest=True), active=True)Positional arguments of filter() are conditions: Q objects, lookups and Exists. Keyword arguments follow them and are joined with AND. exclude() adds the negation of its conditions.
Q() is empty and falsy; combining it with another condition returns the other one:
condition = Q()
for word in words:
condition &= Q(name__icontains=word)
members = Query("club.Member").filter(condition)Lookup keys built at run time
A lookup key is a string built at run time, for example from a filter form, a query string or a viewset. It goes into filter(), exclude(), get() or Q as a keyword argument or as a (key, value) pair and builds the same lookup as the literal keyword:
field, op = "club__name", "icontains"
Member.objects.filter(**{f"{field}__{op}": "chess"}) # same as filter(club__name__icontains="chess")
Member.objects.exclude(Q((f"{field}__{op}", "chess")))
await Member.objects.get(**{"email__iexact": email})Pairs keep their order, keyword arguments are sorted; _connector=Q.OR joins them with OR instead of AND:
params = {"first_name__istartswith": "e", "level__in": ["advanced"], "birth_date__gte": date(1980, 1, 1)}
Member.objects.filter(**params) # all three
Member.objects.filter(Q(*params.items(), _connector=Q.OR)) # any of themDeclared filters (field → allowed lookups) become a condition in a loop:
DECLARED = {"first_name": ["icontains", "istartswith"], "level": ["in"], "birth_date": ["gte", "lte"]}
condition = Q()
for field, ops in DECLARED.items():
for op in ops:
key = f"{field}__{op}"
if key in params:
condition &= Q((key, params[key]))
members = Member.objects.filter(condition)Building a query checks only the key's syntax: segments are identifiers joined with __; any other key ("name__", "tags__0") raises ValueError. Whether the fields exist is checked against the model's schema: resolve_lookup() checks one key, check_lookups() a whole condition. Both raise FieldError without running an operation or taking a slot:
from adorm.query import resolve_lookup
resolve_lookup(Member, "first_name__icontain")
# FieldError: unsupported lookup 'icontain' for clubs.Member.first_name; perhaps you meant 'icontains'
resolve_lookup(Member, "memberships__club__city__icontains").field_type # str (with its max length)
resolve_lookup(Member, "level__in").value_type # Literal['beginner', 'intermediate', 'advanced']A path that ends at a relation takes only exact, gt, gte, lt, lte, in and isnull (club__in). A path cannot continue past a scalar field: adorm has no transforms, and joined__year raises FieldError on every driver. An unchecked key that does not resolve raises FieldError from the driver when it builds the statement; the error does not affect the transaction it is raised in.
Referring to fields with F
F("field") refers to a field of the queried row, for comparisons between fields and arithmetic in the database:
from adorm import F
Query("club.Event").filter(ends__gt=F("starts"))
Query("club.Member").filter(paid__lt=F("fee") * 0.5)
Query("club.Member").annotate(age_next_year=F("age") + 1)F paths cross relations as lookups do: F("club__owner__name").
Functions, aggregates and conditional expressions
Functions take field paths as strings; a literal string is Value("..."):
from adorm import Coalesce, Concat, Lower, Value
Query("club.Member").order_by(Lower("name"))
Query("club.Member").annotate(display=Concat("first_name", Value(" "), "last_name"))
Query("club.Member").annotate(label=Coalesce("nick", "name"))Aggregates take distinct=, filter= and default=:
from adorm import Count, Sum
Query("club.Club").annotate(
members=Count("member", distinct=True),
paid=Sum("member__payment__amount", filter=Q(member__payment__confirmed=True), default=0),
)Case and When choose a value per row; Subquery, OuterRef and Exists correlate a query with the outer one:
from adorm import Case, Exists, OuterRef, Subquery, Value, When
Query("club.Member").annotate(
group=Case(When(age__lt=18, then=Value("junior")), default=Value("senior")),
last_payment=Subquery(
Query("club.Payment").filter(member=OuterRef("pk")).order_by("-paid_at").values("paid_at")[:1]
),
).filter(Exists(Query("club.Payment").filter(member=OuterRef("pk"), confirmed=False)))The expression reference documents every class; Aggregation shows aggregate(), per-row and per-group aggregates, alias() and how to port Django code that aggregates.
Shaping the result
values() and values_list() return dicts or tuples instead of model instances:
Query("club.Member").values("name", "club__name")
Query("club.Member").values_list("name", "age")
Query("club.Member").values_list("pk", flat=True)
Query("club.Member").values_list("name", "age", named=True)only() and defer() choose which fields of model rows are loaded; a deferred field raises AttributeError on access. select_related() loads related rows in the same query:
Query("club.Member").only("name", "email")
Query("club.Member").defer("photo")
Query("club.Member").select_related("club", "club__owner")none() returns no rows, for example as the start of a combination built in a loop.
Combining queries
| and & combine two queries on the same model into one whose condition is the OR or the AND of theirs. & keeps the filters of both, as chained filter() calls do; | joins both conditions into one filter:
adults = Query("club.Member").filter(age__gte=18)
guests = Query("club.Member").filter(guest=True)
(adults | guests).order_by("name")
combined = Query("club.Member").none()
for club in clubs:
combined |= Query("club.Member").filter(club__name=club)Set operations
union(), intersection() and difference() combine the rows of several queries as SQL's UNION, INTERSECT and EXCEPT. Rows are compared column by column: the queries select the same number of columns of matching types, on the same or different models. union() removes duplicate rows unless all=True; intersection() and difference() always remove them:
advanced = Member.objects.filter(level="advanced").values_list("first_name", flat=True)
coaches = Member.objects.filter(tags__contains=["coach"]).values_list("first_name", flat=True)
await advanced.union(coaches).count()
[name async for name in advanced.intersection(coaches)]
[name async for name in advanced.difference(coaches).order_by("first_name")]
cities = Club.objects.values_list("city", flat=True)
[value async for value in cities.union(advanced, all=True)]The combined rows have the row format of the first query: model instances of its model when it selects model rows. After a set operation only order_by(), slicing, values() / values_list() and the reads (async for, get() without conditions, first(), last(), count(), exists()) apply to the combined rows; another set operation nests the combination. filter(), annotate(), distinct(), only(), select_related(), prefetch_related(), aggregate(), update(), delete() and the other methods raise NotSupportedError, as in Django:
members = Member.objects.filter(level="advanced").union(Member.objects.filter(tags__contains=["junior"]))
[member.first_name async for member in members.order_by("-first_name")[:3]]
[name async for name in members.values_list("first_name", flat=True)]
members.filter(club_id=1) # NotSupportedError: cannot call filter() after union()values() or values_list() after a set operation selects those fields in every combined query that selects model rows. After values() or values_list() the ordering names columns of the combined rows, by the names the last values() gives; combined model rows are ordered by fields of the first query's model. Empty queries (none(), qs[0:0]) are combined as Django combines them (Empty queries). A combination inside a filter is a subquery; there each combined query of model rows selects its primary key:
juniors = Member.objects.filter(tags__contains=["junior"]).values("pk")
Member.objects.filter(pk__in=Member.objects.filter(level="advanced").values("pk").union(juniors))Locking rows
select_for_update() locks the selected rows until the end of the transaction that reads them:
Query("club.Member").filter(pk=1).select_for_update(nowait=True)
Query("club.Member").select_related("club").select_for_update(of=("self",), skip_locked=True)The query reference documents nowait, skip_locked, of and no_key.
Running queries
Model.objects returns the model bound to the current session. Its query methods return a QuerySet: every building method of Query plus terminal coroutines that run one operation each (and the prefetches of prefetch_related()):
members = Member.objects.filter(active=True).order_by("name")
async for member in members[:20]: # Select
...
await members.get(pk=5) # Get
await members.count() # Count
await members.filter(age__lt=18).update(junior=True) # UpdatePrefetching related objects
select_related() joins forward relations into the query. prefetch_related() reads related objects of any relation with separate operations after the query's own, one per relation and level, and caches them on the instances; a loop over the instances runs no further operation:
clubs = Club.objects.order_by("slug").prefetch_related("memberships__member", "activities")
async for club in clubs: # clubs, memberships, members, activities
async for membership in club.memberships: # the prefetched rows
member = await membership.member # from the cache
total = await club.activities.count() # the prefetched rows, no CountA Prefetch object chooses the related rows with a query set and can store them under another name (to_attr, a list or an instance on each object); a later lookup continues from that name:
from adorm import Prefetch
attended = Attendance.objects.filter(status="attended").order_by("member_id")
activities = Activity.objects.prefetch_related(
Prefetch("attendances", queryset=attended, to_attr="attended"),
"attended__member",
)
async for activity in activities:
names = [(await attendance.member).first_name for attendance in activity.attended]A generic foreign key is prefetched with one operation per related model, after one for the content types that adorm.content_types has not cached. A next segment continues on each model's relation of that name. GenericPrefetch gives a query set per model:
lines = InvoiceLine.objects.prefetch_related(Prefetch("item", to_attr="billed"), "billed__club")
async for line in lines:
print(type(line.billed).__name__, (await line.billed.club).slug) # "Membership koper-sailing" …A prefetch is a sequence of Select operations that run one after another on the query set's session. Inside a transaction they use its slot and read its writes; outside one, each operation takes and returns a slot. A relation with more keys than BATCH_SIZE is read with one operation per batch. prefetch_related_objects() prefetches for instances read earlier.
Rows by key
in_bulk() returns the instances whose primary key (or another unique field) is in a list, as a dict by that value:
clubs = await Club.objects.in_bulk([1, 2, 99]) # {1: <Club>, 2: <Club>}
by_slug = await Club.objects.in_bulk(["koper-sailing"], field_name="slug")
every = await Club.objects.filter(city="Koper").in_bulk() # every row, by primary keyFrom queries to operations
A query describes rows; an operation is one read or write of them, the unit a driver executes:
from adorm.operations import Count, Delete, Get, Select, Update
members = Query("club.Member").filter(active=True)
Select(members[:20])
Get(members.filter(pk=5))
Count(members)
Update(members.filter(age__lt=18), {"junior": True})
Delete(members.filter(joined__lt=cutoff))An operation converts to JSON-safe data with its values typed by the model schema; decoding restores each value as its field's type. See Operation encoding.
Construct names
A query is a tree of nodes. walk() visits them; constructs() names every construct the tree uses, as a driver declares them in Capabilities.constructs (see Capabilities):
query = Query("club.Member").filter(name__icontains="a").order_by(Lower("name")).distinct()
sorted(set(query.constructs()))
# ['clause.distinct', 'function.Lower', 'lookup.icontains']A set operation adds clause.union, clause.intersection or clause.difference to the construct names of its queries, and clause.combinator.slice when one of them is sliced.