Part III · The ORM

Chapter 10: From QuerySet to SQL

A Query describes a statement as a tree of expressions: the tables it joins, the conditions of its where tree, and what it selects, groups and orders by. A SQLCompiler turns that description into one string of SQL with its parameters, sends it, and hands the rows back with their converters applied. This chapter is how a filter’s name becomes joins and a comparison, what an expression, a lookup, a function and an aggregate write, and how the compiler assembles and runs the statement.

A queryset’s methods do not write SQL. Each leaves something on an object the queryset carries, and nothing is SQL until the queryset is evaluated and asks for the rows. That object is the query, an instance of Query, and it is the first of the two things this chapter is about. The second is the compiler, an instance of SQLCompiler or of one of its subclasses, made for one query and one database connection when the rows are wanted, and thrown away with them. The query is the description; the compiler is the writing and the running. QuerySets tells what the queryset’s methods ask of the query and when the queryset asks for a compiler; this chapter begins where that one stops.

What the query holds is not SQL either. It is a set of Python objects that each know how to write their own piece: a Col for a column, a Value for a parameter, a Func for a function call, a Lookup for a comparison, a WhereNode for the conditions joined by "AND", "OR" or "XOR", a Join for a table joined to another. Each but the Join has the same two methods, and the whole chapter turns on them; the Join has only the one that writes. BaseExpression.resolve_expression binds an object to a query: a name becomes a column of a table the query has joined, and whatever the object holds is bound in turn, so that a resolved object is a copy and the original is left as it was. BaseExpression.as_sql writes the SQL and parameters of a bound object, a Func by compiling its parts and putting them into a template of its own; a backend may supply an as_sqlite, an as_postgresql or the like, and SQLCompiler.compile prefers the one for its connection’s vendor. An expression in this chapter is any object with those two methods. A query is one itself: Query is built on BaseExpression, and inside another query it is a subquery.

The project is the shipping line of Models and fields, which QuerySets records over too: ports, ships and voyages in the application fleet, sailors in crew, and the office’s paperwork in office. The models most of this chapter’s queries run over:

# harbour/fleet/models.py
class Named(models.Model):
    name = models.CharField(max_length=60)

    class Meta:
        abstract = True
        ordering = ["name"]


class Port(Named):
    country = models.CharField(max_length=40)


class Ship(Named):
    tonnage = models.PositiveIntegerField()
    home = models.ForeignKey(Port, on_delete=models.PROTECT, related_name="ships")
    captain = models.OneToOneField("crew.Sailor", null=True, blank=True, on_delete=models.SET_NULL, related_name="command")


class Voyage(models.Model):
    ship = models.ForeignKey(Ship, on_delete=models.CASCADE)
    sailed = models.DateField()
    calls = models.ManyToManyField(Port)


# harbour/crew/models.py
class Sailor(models.Model):
    name = models.CharField(max_length=60)
    ship = models.ForeignKey("fleet.Ship", on_delete=models.CASCADE, related_name="crew")
    signed_on = models.DateTimeField(auto_now_add=True)
    pilots_into = models.ManyToManyField("fleet.Port", through="office.Licence", related_name="pilots")


class Officer(Sailor):
    rank = models.CharField(max_length=10, choices=Rank)


class VeteranManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().filter(signed_on__year__lt=2020)


class Veteran(Sailor):
    objects = VeteranManager()

    class Meta:
        proxy = True


# harbour/office/models.py
class Manifest(models.Model):
    serial = models.CharField(max_length=8, primary_key=True, default=serials.next_serial)
    voyage = models.ForeignKey("fleet.Voyage", on_delete=models.CASCADE)
    crates = models.PositiveIntegerField()
    kilos_each = models.PositiveIntegerField()
    kilos = models.GeneratedField(expression=F("crates") * F("kilos_each"), output_field=models.PositiveIntegerField(), db_persist=True)
    stamped = models.BooleanField(db_default=False)
    scan = models.FileField(upload_to="manifests/", blank=True)

Left out are the docstrings and the __str__ methods, the constraints and the index of a ship, the manager of a sailor and the choices of a rank, and the rest of the office: the Licence that lets a sailor pilot ships into a port, the Logbook and its LogEntry, and the Berth, whose primary key is two columns, which is shown where a section turns on it. A port and a ship are ordered by name unless a queryset says otherwise, and a sailor has no ordering; a Veteran is a sailor who signed on before 2020, by its manager’s filter. The recordings were made in one run, which began with these rows:

The rows the recording begins with
    Port: 1 Bergen (Norway), 2 Leith (Scotland), 3 Lisbon (Portugal)
    Ship: 1 Petrel (480 tons, home Bergen, captain none), 2 Gannet (1200 tons, home Leith, captain Cora)
    Sailor: 1 Ada (ship Petrel, signed on 2011), 2 Bram (ship Petrel, signed on 2026), 3 Cora (ship Gannet, signed on 2011), 4 Dag (ship Gannet, signed on 2026)
    Officer: 3 Cora (master)
    Voyage: 1 (ship Petrel, sailed 2026-03-02, calls at Leith and Lisbon), 2 (ship Gannet, sailed 2025-10-06, calls at Bergen)
    Licence: 1 (Ada into Bergen)
    Berth: Bergen 1 (90 m), Bergen 2 (140 m)
    Logbook: 25, from 1 Log 1 to 25 Log 25
    Manifest: M-0001 (voyage 1, 40 crates of 25 kg, 1000 kg, stamped False)

The recordings ran on SQLite. In a recording, a line at the left is a line of Python as it was run; where it is an expression, its value follows ->, or the word “raises” and the exception. The lines set in under it are the calls it set going, nested as they were made, with only the calls the passage is about written down, and -> there is what a call returned, put into words as it was returned. A line of three dots stands for lines of the block left out. Some lines ask through helpers of the recording’s own: a line beginning sql_of( answers with a queryset’s statement and its parameters, tail( with the statement from its FROM on and the parameters after the select list’s, where_said( with a queryset’s where tree, entry( with a child of a queryset’s where tree, and brief( or said( with an object in a few words. A line that begins SQL is a statement as Django handed it to its cursor, with Django’s %s for a parameter. An expression is written as its repr, which for most is the call that would make it; a where tree as (AND: ...); a step of a path through the models as PathInfo(from Sailor to Ship via Sailor.ship, direct, one); and an entry of the query’s alias map as Join(fleet_ship, from crew_sailor via Sailor.ship, INNER JOIN, not nullable). What such a line shows of SQL’s dialect is this backend’s.

In this chapter

10.1 The query: what a Query holds, and how it is copied A Query is the description of one statement, one that holds no connection: the tables it reads under their aliases, its where tree, what it selects and annotates, how it groups, orders and limits. Query.clone copies it cheaply enough that every method of a queryset can work on a copy, and Query.chain makes the copy ready for the next method, resetting what one method must not inherit from the last.
10.2 Expressions: resolve_expression and as_sql An expression is any object with a resolve_expression method, which binds it to a query, and whose resolved form has an as_sql method, which writes its SQL: F, Value, Col, the CombinedExpression that + makes, Case, and everything else a Query holds. This page is the protocol BaseExpression gives them all, what the operators of Combinable make, how an expression learns its type and its equality, and the small classes of django/db/models/expressions.py.
10.3 From a name to a column: names_to_path, setup_joins and build_lookup Query.names_to_path reads the words of a filter’s name against the models and returns the relations it crosses as a path; Query.setup_joins turns the path into joins, and Query.trim_joins takes back a join at the end whose only use is a key the previous table already holds; Query.build_lookup and Query.try_transform make transforms and a lookup of the words that are no fields. Query.solve_lookup_type puts a name to the annotations before the models, and Query.resolve_ref reads the name an F carries the same way.
10.4 Tables and joins: the alias map and join promotion Query.alias_map is the query’s record of every table its statement joins, each under an alias and each but the first a Join from a table already there; Query.join adds a join to it or reuses one, Query.table_alias names it, and Query.alias_refcount counts what refers to it. Whether a join is written "INNER JOIN" or "LEFT OUTER JOIN" is decided by the conditions together, through JoinPromoter, Query.promote_joins and Query.demote_joins; Query.bump_prefix and Query.change_aliases rename the aliases of a query that is to stand inside another.
10.5 The where clause: build_filter, add_q and WhereNode Query.add_q turns a Q into the query’s where tree: a WhereNode for each node of the Q, and for each pair of a name and a value a lookup built by Query.build_filter. WhereNode.as_sql writes the tree, counting the children that can match nothing or everything; Query.split_exclude turns an exclude across a relation that can reach several rows into a subquery under "NOT EXISTS"; and Query.add_filtered_relation is the query’s side of a condition that goes into a join.
10.6 Lookups: Lookup, BuiltinLookup and the standard comparisons A Lookup is the comparison at the end of a filter’s name: a left side, a right side prepared for the left side’s type, and an as_sql that writes the two with an operator between. BuiltinLookup takes the operator from the backend’s table, and the classes of django/db/models/lookups.py registered on Field, from Exact and In to IsNull and IRegex, are what every field answers to, with the integer, year and UUID lookups registered beside them.
10.7 Transforms and database functions: Transform and Func Func is a function call as an expression: a function’s name, a template, and the arguments compiled into it. Transform is a Func of one argument that the word of a filter’s name can reach, "lower" in "name__lower", and that carries a registry of its own for the word after it (Func, Transform). This page is the two classes and the functions Django ships in django/db/models/functions/: the comparisons and casts, the parts and truncations of a date, the text and the mathematical functions, JSON, UUIDs, and the window functions.
10.8 Lookups on relations and composite keys: RelatedIn and the tuple lookups ForeignObject registers lookup classes of its own, RelatedExact, RelatedIn, RelatedIsNull and four comparisons, which take an instance, a key or a queryset on the right of a relation’s name and compare the column that holds the key. CompositePrimaryKey registers TupleExact, TupleIn and their relatives, which compare several columns at once as a row value, or as a chain of one-column comparisons where the backend has no row values.
10.9 The compiler: as_sql, execute_sql and results_iter A SQLCompiler is made for one query and one connection when the rows are wanted. SQLCompiler.as_sql assembles the statement from the query’s clauses, in an order that is not the statement’s; SQLCompiler.execute_sql sends it on a cursor and returns rows, one row, a row count, the cursor itself or nothing; and SQLCompiler.results_iter hands each row on with its converters applied.
10.10 The SELECT clause: get_select, the select mask and klass_info SQLCompiler.get_select decides which columns a statement selects and under what alias, from the query’s Query.select and Query.selected, its annotations and extras, and the select mask, the fields that defer and only leave to be loaded; with the list it builds klass_info, the map by which a row is cut into an instance of the model and of each related model that select_related asked for.
10.11 ORDER BY and DISTINCT: get_order_by, find_ordering_name and get_distinct A statement’s "ORDER BY" comes from one of three places on the query, Query.extra_order_by, Query.order_by or the model’s own ordering, in that precedence; SQLCompiler._order_by_pairs turns each item into an OrderBy, SQLCompiler.find_ordering_name follows a relation in a name to the related model’s ordering, and SQLCompiler.get_order_by resolves, deduplicates and compiles them. SQLCompiler.get_distinct writes the columns of a "DISTINCT ON", and SQLCompiler.get_extra_select adds to the select list what a plain "DISTINCT" needs the ordering’s columns for.
10.12 GROUP BY and HAVING: set_group_by, get_group_by and split_having_qualify Query.group_by records that a query groups, in one of three forms, and Query.set_group_by fixes the grouping as a tuple of expressions; in the compiler, SQLCompiler.get_group_by writes the "GROUP BY" clause from that tuple, the select list, the ordering and the having, and WhereNode.split_having_qualify divides the where tree between "WHERE", "HAVING" and the part that refers to a window.
10.13 Aggregates: Aggregate, Count and get_aggregation Aggregate is the Func that folds the rows of a group into one value, "COUNT", "SUM", "STRING_AGG", and is marked as one, so that the query groups for it and a filter on it goes to "HAVING". Query.get_aggregation runs a set of aggregates over a query for QuerySet.aggregate and QuerySet.count, in the query’s own statement where that gives the right answer and otherwise in a statement wrapped around it; Query.exists makes the narrowed query that QuerySet.exists sends.
10.14 Subqueries: Subquery, Exists, OuterRef and a query inside another A Query is an expression, and Query.resolve_expression is how one comes to stand inside another: a clone whose aliases bump_prefix has renamed, which notes the outer query’s tables as external_aliases, and in whose conditions the ResolvedOuterRef an OuterRef left has become a column of the outer table. Subquery and Exists wrap such a query to stand as a selected column or a condition; a queryset given as the value of a filter goes the same way with no wrapper at all.
10.15 Window expressions: Window, WindowFrame and the QUALIFY rewrite Window is an expression whose function is applied over a partition of the rows and written as function OVER (...); WindowFrame, with its two classes RowRange and ValueRange, says which rows of the partition the function sees. A condition on a window cannot stand in a statement’s "WHERE", so where a filter names one, WhereNode.split_having_qualify sets the condition aside and SQLCompiler.get_qualify_sql writes the statement inside another and tests the condition there.
10.16 Two queries into one: combine and get_combinator_sql Query.combine merges a second query into a first: it brings the second query’s joins into the first’s alias map and adds its where tree under a connector, and it is what &, | and ^ between two querysets come to. SQLCompiler.get_combinator_sql does the other thing SQL can do with two queries: it keeps the parts of a combined query whole, compiles each with a compiler of its own, and joins the statements with the backend’s word for a union, an intersection or a difference, "UNION", "INTERSECT" and "EXCEPT" in the base class.
10.17 INSERT, UPDATE and DELETE: the insert, update and delete compilers An insert, an update and a delete have a Query subclass each, InsertQuery, UpdateQuery and DeleteQuery, and a compiler each, SQLInsertCompiler, SQLUpdateCompiler and SQLDeleteCompiler (SQLInsertCompiler, SQLUpdateCompiler, SQLDeleteCompiler).

Building: a filter becomes joins and a comparison

QuerySet.filter reduces its arguments to a Q, a tree of pairs of a name and a value, and hands it to Query.add_q (filter, exclude and Q objects). Everything from there on is the query’s. Here is one filter with two conditions, one across two relations and one with a transform and a lookup in its name:

bergen_veterans = Sailor.objects.filter(ship__home__name='Bergen', signed_on__year__lt=2020)
  QuerySet.filter(ship__home__name='Bergen', signed_on__year__lt=2020)
    QuerySet._filter_or_exclude_inplace(negate=False, args=(), kwargs={'ship__home__name': 'Bergen', 'signed_on__year__lt': 2020})
      Query.add_q(q_object=<Q: (AND: ('ship__home__name', 'Bergen'), ('signed_on__year__lt', 2020))>)
        Query._add_q(<Q: (AND: ('ship__home__name', 'Bergen'), ('signed_on__year__lt', 2020))>, used_aliases=set())
          JoinPromoter.__init__(connector='AND', num_children=2, negated=False)
          Query.build_filter(('ship__home__name', 'Bergen'), can_reuse=set())
            Query.solve_lookup_type(lookup='ship__home__name')
              Query.names_to_path(names=['ship', 'home', 'name'])  ->  ([PathInfo(from Sailor to Ship via Sailor.ship, direct, one), PathInfo(from Ship to Port via Ship.home, direct, one)], Port.name, (Port.name,), [])
              -> ([], ['ship', 'home', 'name'], False)
            Query.join(join=BaseTable(crew_sailor))
              Query.table_alias(table_name='crew_sailor', create=True)  ->  ('crew_sailor', True)
              -> 'crew_sailor'
            Query.setup_joins(names=['ship', 'home', 'name'], alias='crew_sailor', can_reuse=set())
              Query.names_to_path(names=['ship', 'home', 'name'], fail_on_missing=True)  ->  ([PathInfo(from Sailor to Ship via Sailor.ship, direct, one), PathInfo(from Ship to Port via Ship.home, direct, one)], Port.name, (Port.name,), [])
              Query.join(join=Join(fleet_ship, from crew_sailor via Sailor.ship, INNER JOIN, not nullable))
                Query.table_alias(table_name='fleet_ship', create=True)  ->  ('fleet_ship', True)
                -> 'fleet_ship'
              Query.join(join=Join(fleet_port, from fleet_ship via Ship.home, INNER JOIN, not nullable))
                Query.table_alias(table_name='fleet_port', create=True)  ->  ('fleet_port', True)
                -> 'fleet_port'
              -> JoinInfo(final_field=Port.name, targets=(Port.name,), opts=the options of Port, joins=['crew_sailor', 'fleet_ship', 'fleet_port'], path=[PathInfo(from Sailor to Ship via Sailor.ship, direct, one), PathInfo(from Ship to Port via Ship.home, direct, one)])
            Query.check_related_objects(field=Port.name, value='Bergen', opts=the options of Port)
            Query.trim_joins(targets=(Port.name,), joins=['crew_sailor', 'fleet_ship', 'fleet_port'], path=[PathInfo(from Sailor to Ship via Sailor.ship, direct, one), PathInfo(from Ship to Port via Ship.home, direct, one)])  ->  ((Port.name,), 'fleet_port', ['crew_sailor', 'fleet_ship', 'fleet_port'])
            Query.build_lookup(lookups=[], lhs=Col(fleet_port, fleet.Port.name), rhs='Bergen')
              Exact.__init__(lhs=Col(fleet_port, fleet.Port.name), rhs='Bergen')  (Lookup.__init__)
                Exact.get_prep_lookup  (Lookup.get_prep_lookup)  ->  'Bergen'
              -> Exact(Col(fleet_port, fleet.Port.name), 'Bergen')
            -> ((AND: Exact(Col(fleet_port, fleet.Port.name), 'Bergen')), {'crew_sailor', 'fleet_port', 'fleet_ship'})
...
          Query.build_filter(('signed_on__year__lt', 2020), can_reuse={'crew_sailor', 'fleet_port', 'fleet_ship'})
            Query.solve_lookup_type(lookup='signed_on__year__lt')
              Query.names_to_path(names=['signed_on', 'year', 'lt'])  ->  ([], Sailor.signed_on, (Sailor.signed_on,), ['year', 'lt'])
              -> (['year', 'lt'], ['signed_on'], False)
            Query.resolve_lookup_value(value=2020, can_reuse={'crew_sailor', 'fleet_port', 'fleet_ship'}, allow_joins=True)  ->  2020
            Query.setup_joins(names=['signed_on'], alias='crew_sailor', can_reuse={'crew_sailor', 'fleet_port', 'fleet_ship'})
              Query.names_to_path(names=['signed_on'], fail_on_missing=True)  ->  ([], Sailor.signed_on, (Sailor.signed_on,), [])
              -> JoinInfo(final_field=Sailor.signed_on, targets=(Sailor.signed_on,), opts=the options of Sailor, joins=['crew_sailor'], path=[])
            Query.check_related_objects(field=Sailor.signed_on, value=2020, opts=the options of Sailor)
            Query.trim_joins(targets=(Sailor.signed_on,), joins=['crew_sailor'], path=[])  ->  ((Sailor.signed_on,), 'crew_sailor', ['crew_sailor'])
            Query.build_lookup(lookups=['year', 'lt'], lhs=Col(crew_sailor, crew.Sailor.signed_on), rhs=2020)
              Query.try_transform(lhs=Col(crew_sailor, crew.Sailor.signed_on), name='year', lookups=['year', 'lt'])  ->  ExtractYear(Col(crew_sailor, crew.Sailor.signed_on))
              YearLt.__init__(lhs=ExtractYear(Col(crew_sailor, crew.Sailor.signed_on)), rhs=2020)  (Lookup.__init__)
                YearLt.get_prep_lookup  (Lookup.get_prep_lookup)  ->  2020
              -> YearLt(ExtractYear(Col(crew_sailor, crew.Sailor.signed_on)), 2020)
            -> ((AND: YearLt(ExtractYear(Col(crew_sailor, crew.Sailor.signed_on)), 2020)), {'crew_sailor'})
...
          JoinPromoter.update_join_types(a Query of Sailor)
            Query.promote_joins(set())
            Query.demote_joins({'crew_sailor', 'fleet_port', 'fleet_ship'})

Query.add_q walks the Q with Query._add_q, which makes a WhereNode for the tree’s node and calls Query.build_filter for each leaf. For one pair, build_filter does three things in turn.

The name is read against the models. Query.solve_lookup_type splits it at each "__" and has Query.names_to_path walk the words as fields: "ship" is a foreign key of Sailor, "home" a foreign key of Ship, "name" a field of Port, and the walk stops at the first field that is no relation, or at a word that is no field, leaving ["year", "lt"] over for "signed_on"; the False that ends each of those lines says the name was not an annotation’s, which would stand there in its place. What the walk returns is a path, one PathInfo or more for each relation crossed, each saying which models it joins, through which field, in which direction and whether it can reach more than one row. Query.setup_joins turns the path into joins, and Query.trim_joins takes back any join at the end whose only use is a key the previous table already holds, which is why filter(ship=petrel) joins nothing and compares the column "ship_id" (From a name to a column: names_to_path, setup_joins and build_lookup).

The joins go into the alias map. Query.join gives each Join an alias, ordinarily the table’s own name the first time a table appears and T with a number after that, and keeps it in Query.alias_map with a count of how many conditions and columns refer to it; a join made for a condition that is later dropped is left in the map with a count of zero and is not written. A join starts as "INNER JOIN" unless the relation can have no row on the far side, a nullable foreign key or a reverse relation, or the join it hangs from is outer already, in which case it starts as "LEFT OUTER JOIN"; and as the conditions are added a JoinPromoter counts, for each join, how many of a node’s children need a row on the far side, and demotes the join to inner under an "AND" or promotes it to outer under an "OR" that not every child shares. That is the vote the two conditions above took on the joins of the ship and the port, which were inner as they were made and stayed so, and Tables and joins: the alias map and join promotion has the rules.

The words left over become a lookup. Query.build_lookup takes every word but the last for a transform, an expression made of the column, and the last for a lookup, a comparison of the column, or of the transformed column, with the value: "year" finds ExtractYear in the registry of the datetime field, and "lt" finds YearLt in the registry of ExtractYear; a last word that no lookup answers to is taken for one more transform and compared by "exact", which is also the lookup where no word is left. The registry is told in The lookup registry: RegisterLookupMixin; the classes it finds are this chapter’s. A Lookup holds a left side and a right side, prepares the right side through the left side’s field, and writes "column = %s" from the backend’s table of operators (Lookups: Lookup, BuiltinLookup and the standard comparisons); the comparisons registered on a relation and on a composite primary key are a few more (Lookups on relations and composite keys: RelatedIn and the tuple lookups). A transform is a Func with one argument that also has a registry of its own, and the database functions of django.db.models.functions are built on the same class (Transforms and database functions: Transform and Func).

The lookup is wrapped in a WhereNode of its own and added to the node of its Q. The tree the filter leaves is an "AND" of the two lookups, the Exact and the YearLt, and Query.where holds it. A negation is kept on the node, and build_filter adds an "IS NOT NULL" beside a negated comparison of a column that can be null, in itself or by an outer join so that the SQL agrees with Python’s not; an exclude across a relation that can reach several rows is turned into a subquery under "NOT EXISTS", since a negated join would exclude a ship for the one sailor whose name matches and keep it for the others (The where clause: build_filter, add_q and WhereNode).

What else the query is given

The other methods of the queryset leave other things on the query, and most of them are expressions too. An annotation is an expression resolved against the query and kept in Query.annotations under its alias; an aggregate among them sets Query.group_by (Aggregates: Aggregate, Count and get_aggregation, GROUP BY and HAVING: set_group_by, get_group_by and split_having_qualify). A queryset given as a value, or a Subquery or an Exists, carries a query of its own, which is resolved against the outer one, with its aliases renamed so that the two do not collide and the outer query’s columns reachable through OuterRef (Subqueries: Subquery, Exists, OuterRef and a query inside another). A Window is an expression whose function runs over a partition of the rows, and a filter on one cannot stand in a "WHERE", so the compiler wraps the statement in another (Window expressions: Window, WindowFrame and the QUALIFY rewrite). The ordering is kept as names and expressions and resolved only when the statement is written; values leaves the columns to select and their names; defer and only leave a set of field names to leave out or to keep. What a Query holds, how Query.clone copies it for the next method and what Query.chain resets, is The query: what a Query holds, and how it is copied.

An expression can be built without a query and used on several. F("tonnage") + 1 is a CombinedExpression of an F and a Value; resolving it against a query of ships turns the F into a Col of the ships’ table through Query.resolve_ref, and the type of the sum, when it is wanted, is asked of the pair of field types. Two expressions built the same way are equal and hash alike, which is what lets the compiler notice that an ordering refers to a selected column. Expressions: resolve_expression and as_sql is the protocol in full, with F, Value, Col, Case and the rest of django/db/models/expressions.py.

Compiling: the compiler assembles the statement

When the queryset is evaluated, Query.get_compiler asks the connection’s operations object for the class named by Query.compiler, "SQLCompiler" for a select, and makes one for the query and the connection. The compiler’s SQLCompiler.as_sql is where the query becomes a string. The same query as above, ordered and limited, compiled:

compiler.as_sql()
  SQLCompiler.as_sql
    SQLCompiler.pre_sql_setup
      SQLCompiler.setup_query
        SQLCompiler.get_select
          SQLCompiler.get_default_columns  ->  [Col(crew_sailor, crew.Sailor.id), Col(crew_sailor, crew.Sailor.name), Col(crew_sailor, crew.Sailor.ship), Col(crew_sailor, crew.Sailor.signed_on)]
          -> (4 columns, klass_info for Sailor, {})
      SQLCompiler.get_order_by
        SQLCompiler._order_by_pairs
          SQLCompiler.find_ordering_name(name='-signed_on')
            Query.setup_joins(names=['signed_on'], alias='crew_sailor')  ->  JoinInfo(final_field=Sailor.signed_on, targets=(Sailor.signed_on,), opts=the options of Sailor, joins=['crew_sailor'], path=[])
            Query.trim_joins(targets=(Sailor.signed_on,), joins=['crew_sailor'], path=[])  ->  ((Sailor.signed_on,), 'crew_sailor', ['crew_sailor'])
            -> [OrderBy(Col(crew_sailor, crew.Sailor.signed_on), descending=True)]
        SQLCompiler.compile(OrderBy(Col(crew_sailor, crew.Sailor.signed_on), descending=True))
          OrderBy.as_sql
            -> ('"crew_sailor"."signed_on" DESC', ())
          -> ('"crew_sailor"."signed_on" DESC', ())
        -> ['"crew_sailor"."signed_on" DESC']
      WhereNode.split_having_qualify  ->  ((AND: Exact(Col(fleet_port, fleet.Port.name), 'Bergen'), YearLt(ExtractYear(Col(crew_sailor, crew.Sailor.signed_on)), 2020)), None, None)
      SQLCompiler.get_extra_select  ->  []
      SQLCompiler.get_group_by  ->  []
      -> (extra_select of 0, order_by of 1, group_by of 0)
    SQLCompiler.get_distinct  ->  ([], [])
    SQLCompiler.get_from_clause
      SQLCompiler.compile(BaseTable(crew_sailor))
        BaseTable.as_sql  ->  ('"crew_sailor"', [])
        -> ('"crew_sailor"', [])
      SQLCompiler.compile(Join(fleet_ship, from crew_sailor via Sailor.ship, INNER JOIN, not nullable))
        Join.as_sql
          -> ('INNER JOIN "fleet_ship" ON ("crew_sailor"."ship_id" = "fleet_ship"."id")', [])
        -> ('INNER JOIN "fleet_ship" ON ("crew_sailor"."ship_id" = "fleet_ship"."id")', [])
      SQLCompiler.compile(Join(fleet_port, from fleet_ship via Ship.home, INNER JOIN, not nullable))
        Join.as_sql
          -> ('INNER JOIN "fleet_port" ON ("fleet_ship"."home_id" = "fleet_port"."id")', [])
        -> ('INNER JOIN "fleet_port" ON ("fleet_ship"."home_id" = "fleet_port"."id")', [])
      -> (['"crew_sailor"', 'INNER JOIN "fleet_ship" ON ("crew_sailor"."ship_id" = "fleet_ship"."id")', 'INNER JOIN "fleet_port" ON ("fleet_ship"."home_id" = "fleet_port"."id")'], [])
    SQLCompiler.compile((AND: Exact(Col(fleet_port, fleet.Port.name), 'Bergen'), YearLt(ExtractYear(Col(crew_sailor, crew.Sailor.signed_on)), 2020)))
      WhereNode.as_sql
        SQLCompiler.compile(Exact(Col(fleet_port, fleet.Port.name), 'Bergen'))
          Exact.as_sql  (BuiltinLookup.as_sql)
            Exact.process_lhs  (BuiltinLookup.process_lhs)
              Exact.process_lhs  (Lookup.process_lhs)
                -> ('"fleet_port"."name"', ())
              -> ('"fleet_port"."name"', ())
            Exact.process_rhs  (Lookup.process_rhs)  ->  ('%s', ['Bergen'])
            -> ('"fleet_port"."name" = %s', ('Bergen',))
          -> ('"fleet_port"."name" = %s', ('Bergen',))
        SQLCompiler.compile(YearLt(ExtractYear(Col(crew_sailor, crew.Sailor.signed_on)), 2020))
          YearLt.as_sql  (YearLookup.as_sql)
            YearLt.process_lhs(lhs=Col(crew_sailor, crew.Sailor.signed_on))  (BuiltinLookup.process_lhs)
              YearLt.process_lhs(lhs=Col(crew_sailor, crew.Sailor.signed_on))  (Lookup.process_lhs)
                -> ('"crew_sailor"."signed_on"', ())
              -> ('"crew_sailor"."signed_on"', ())
            YearLt.process_rhs  (Lookup.process_rhs)  ->  ('%s', [2020])
            -> ('"crew_sailor"."signed_on" < %s', ('2020-01-01 00:00:00',))
          -> ('"crew_sailor"."signed_on" < %s', ('2020-01-01 00:00:00',))
        -> ('("fleet_port"."name" = %s AND "crew_sailor"."signed_on" < %s)', ['Bergen', '2020-01-01 00:00:00'])
      -> ('("fleet_port"."name" = %s AND "crew_sailor"."signed_on" < %s)', ['Bergen', '2020-01-01 00:00:00'])
    BaseDatabaseOperations.limit_offset_sql(low_mark=0, high_mark=5)  ->  'LIMIT 5'
    Query.reset_refcounts({'crew_sailor': 2, 'fleet_ship': 1, 'fleet_port': 1})
    -> ('SELECT "crew_sailor"."id", "crew_sailor"."name", "crew_sailor"."ship_id", "crew_sailor"."signed_on" FROM "crew_sailor" INNER JOIN "fleet_ship" ON ("crew_sailor"."ship_id" = "fleet_ship"."id") INNER JOIN "fleet_port" ON ("fleet_ship"."home_id" = "fleet_port"."id") WHERE ("fleet_port"."name" = %s AND "crew_sailor"."signed_on" < %s) ORDER BY "crew_sailor"."signed_on" DESC LIMIT 5', ('Bergen', '2020-01-01 00:00:00'))

The clauses are made in an order that is not the statement’s. SQLCompiler.pre_sql_setup first has SQLCompiler.get_select settle the columns, every concrete field of the model where nothing narrows them, then has SQLCompiler.get_order_by resolve the ordering, which may join a table of its own and which get_group_by has to see, then splits the where tree into the conditions that go into "WHERE", those that compare an aggregate and go into "HAVING", and those that compare a window function, which are left for a statement wrapped round this one, and then has SQLCompiler.get_group_by write the grouping. Only after that does as_sql read the alias map for the "FROM" clause, since the select and the ordering may have added joins, compile the where tree, and put the pieces together as "SELECT", "FROM", "WHERE", "GROUP BY", "HAVING", "ORDER BY" and the limit, gathering the parameters in the same order so that each %s meets its value. On the way out it puts back the reference counts of the aliases as they were, since resolving the ordering counts the tables it walks and may join one the query did not have before. The compiler: as_sql, execute_sql and results_iter tells the assembly; The SELECT clause: get_select, the select mask and klass_info, ORDER BY and DISTINCT: get_order_by, find_ordering_name and get_distinct and GROUP BY and HAVING: set_group_by, get_group_by and split_having_qualify tell the clauses.

Each node writes its own SQL. SQLCompiler.compile calls a node’s as_sql, or its method for the connection’s vendor where it has one, and a node compiles its children the same way: the where tree compiles each lookup, a lookup compiles its left side and prepares its right, and a Col writes its alias and column, quoted by the backend. The year comparison is the one surprise in the statement above: YearLt compares the column itself with the first day of the year, not the year extracted from it, so that an index on the column can serve.

Figure 10.1 The two halves of the way from a filter to a statement. On the left, what one call of filter leaves on the query; on the right, what the compiler does with it when the queryset is evaluated. The query is carried from one method to the next, a copy at each; the compiler is made when the rows are wanted and reads the query once.

Running: `execute_sql` and the rows

SQLCompiler.execute_sql calls as_sql, takes a cursor from the connection, runs the statement, and returns what the caller asked for: the rows a chunk at a time for an iteration, one row for an aggregate, the row count for an update, the cursor itself, or nothing. Where the where tree can match nothing, which a comparison with an empty list makes it, as_sql raises EmptyResultSet, and execute_sql answers with no rows and sends nothing. SQLCompiler.results_iter reads the rows and applies to each column its converters, the functions the backend and the column’s field or expression supply for turning what the driver returned into what the program is to have: a datetime from the text SQLite stores, a bool from an integer, a float from an expression cast to one. The rows go to the queryset’s iterable class, which makes instances of them with the compiler’s klass_info saying which columns are whose (From rows to instances: ModelIterable and select_related).

rows = list(compiler.results_iter())
  SQLCompiler.results_iter
    SQLCompiler.execute_sql
      SQLCompiler.as_sql  (as above)  ->  ('SELECT "crew_sailor"."id", "crew_sailor"."name", "crew_sailor"."ship_id", "crew_sailor"."signed_on" FROM "crew_sailor" INNER JOIN "fleet_ship" ON ("crew_sailor"."ship_id" = "fleet_ship"."id") INNER JOIN "fleet_port" ON ("fleet_ship"."home_id" = "fleet_port"."id") WHERE ("fleet_port"."name" = %s AND "crew_sailor"."signed_on" < %s) ORDER BY "crew_sailor"."signed_on" DESC LIMIT 5', ('Bergen', '2020-01-01 00:00:00'))
      CursorWrapper.execute
        SQL SELECT "crew_sailor"."id", "crew_sailor"."name", "crew_sailor"."ship_id", "crew_sailor"."signed_on" FROM "crew_sailor" INNER JOIN "fleet_ship" ON ("crew_sailor"."ship_id" = "fleet_ship"."id") INNER JOIN "fleet_port" ON ("fleet_ship"."home_id" = "fleet_port"."id") WHERE ("fleet_port"."name" = %s AND "crew_sailor"."signed_on" < %s) ORDER BY "crew_sailor"."signed_on" DESC LIMIT 5 with params ('Bergen', '2020-01-01 00:00:00')
    SQLCompiler.get_converters  ->  {3: [DatabaseOperations.convert_datetimefield_value]}
  SQLCompiler.apply_converters
rows  ->  [[1, 'Ada', 1, datetime.datetime(2011, 6, 1, 8, 0, tzinfo=datetime.timezone.utc)]]

The other statements

Two queries can be made one. The operators & and | of a queryset call Query.combine, which brings the right side’s joins into the left side’s alias map, making them afresh under an "AND" and reusing them under an "OR", and adds its where tree under the connector; union and its relatives keep the queries side by side, and SQLCompiler.get_combinator_sql compiles each with a compiler of its own and joins the statements with the operator (Two queries into one: combine and get_combinator_sql). An aggregate asked of a queryset goes through Query.get_aggregation, which makes the aggregates the query’s only selected annotations, or wraps the query as a subquery where it is sliced, distinct, combined or grouped already, and in a few cases more (Aggregates: Aggregate, Count and get_aggregation).

An insert, an update and a delete have a Query subclass each, InsertQuery, UpdateQuery and DeleteQuery, and a compiler each. SQLInsertCompiler.as_sql takes each field’s value from each instance through the field’s pre_save and prepares it, writes one "VALUES" list for all the rows where the backend can take one, and asks for the columns the database fills in, an automatic key among them, back with "RETURNING" where it can. SQLUpdateCompiler.as_sql writes a "SET" of prepared values and compiled expressions, and where the filter joins another table, moves the joins into a subquery on the primary key first, or, where the backend cannot select from the table it is updating or the update reaches a parent’s table too, into a select that is run beforehand and whose keys the update then names. SQLDeleteCompiler.as_sql moves a delete’s joins into a subquery in the same way (INSERT, UPDATE and DELETE: the insert, update and delete compilers).

The neighbours

The query is built from the model’s _meta: names_to_path asks the options for a field by name, a relation field for its path, and a child model for the path to its parent (The options: what a model keeps as _meta, Relations: a field, its rel and the class at the other end, Inheritance: abstract bases, parents with tables and proxies). A lookup prepares its value through the field’s get_prep_value and get_db_prep_value, and the field’s converters are its own (A field: the base class). The registry a word is looked up in is RegisterLookupMixin, which QuerySets tells.

The compiler leans on the connection at every turn, and that is Database backends: the operations object supplies the compiler class, the quoting of a name, the SQL of a limit, of "DISTINCT ON" and of "FOR UPDATE", and the converters of a column’s type; the connection itself holds the table of operators a BuiltinLookup writes from; the features object says what the backend can do, from a "RETURNING" clause to a tuple comparison, and a statement is written one way or another by it; and the cursor the compiler runs the statement on is the connection’s. The lookups and transforms of a JSON field are built on the classes here, as are those of django.contrib.postgres and of a geometry, which Content types, static files and the other contrib apps has.