Skillversitas

Joins and Subqueries

Joins are how a query crosses a relationship in a single SQL statement instead of one query per row: Article.select().join(Author) produces the JOIN authors that brings the author's columns into the article query. Subqueries are how one query feeds another - a filter that references the result of a nested select, or a select that includes a computed column. Together they cover the two shapes of "the answer needs more than one table," and both are built from the same expression objects the where chapter used. This chapter covers the join types, filtering across the join, the N+1 problem and its prefetch fix, and subquery patterns.

The join() Call and Join Types

from peewee import *

# an inner join: articles WITH their authors (the FK is the condition)
q = (Article.select(Article.title, Author.name)
           .join(Author)          # JOIN authors ON article.author_id = authors.id
           .where(Author.name == "Ada")
           .execute())

# left outer join: articles EVEN IF the author row is missing
q2 = (Article.select(Article.title, Author.name)
            .join(Author, JOIN.LEFT)
            .execute())

# explicit condition when the join is not the obvious FK:
q3 = (Article.select()
         .join(Author, on=(Article.title == Author.name))
         .execute())

print(q)   # SELECT ... FROM article INNER JOIN authors ...

.join(Model) adds the join for the relationship between the query's model and Model - Peewee derives the ON condition from the foreign key, so the common case is one argument. .join(Model, JOIN.LEFT) makes it an outer join (rows without a match are kept, the other side's columns NULL), and .join(Model, on=condition) states the condition explicitly for the non-FK joins. Multiple .join() calls chain, walking a path of relationships (article -> author -> profile). The filter after the join can reference either side's fields - where(Author.name == "Ada") is the join doing its job: filtering articles by a property of their author in one statement. print(q) shows the JOIN and its ON, which is the inspection that confirms the join is on the column you think.

The N+1 Problem and prefetch

# the N+1: a list of articles, each accessing its author
rows = Article.select().where(Article.published == True).execute()
for art in rows:
    print(art.title, art.author.name)   # 1 query for the list
                                          # + 1 query PER row for .author

# the fix: prefetch loads the related rows in a second query,
# and the .author access in the loop is served from memory
rows = Article.select().prefetch(Article.author).execute()
for art in rows:
    print(art.title, art.author.name)   # 2 queries total, always

# prefetch a backref: the articles for each author in the list
authors = Author.select().prefetch(Author.articles).execute()
for a in authors:
    for art in a.articles:              # in-memory, no per-author query
        print(a.name, art.title)

The N+1 is the most common performance bug in ORM code: one query for the list, then one per row for the related attribute accessed in the loop. prefetch is Peewee's fix - it runs the main query and then one additional query for the related rows (all of them, filtered to the parents in the result), and the relationship access in the loop is served from that preloaded data instead of issuing a query. The shape is .prefetch(Article.author) for the forward FK or .prefetch(Author.articles) for the backref, and it composes (.prefetch(Article.author, Article.tags) for several related sets). The decision is the performance chapter's standing rule: a loop that touches a relationship on each row either joins (when the query filters or orders on the related data) or prefetches (when it only displays it) - and the .explain() habit is what reveals the extra queries when you are not sure.

Subqueries: A Query Inside a Query

from peewee import *

# a subquery as a filter: articles by authors who wrote > 3 posts
author_ids = (Author.select(Author.id)
                   .switch()
                   .join(Article)
                   .group_by(Author.id)
                   .having(Sql("COUNT(article.id)") > 3))

rows = (Article.select()
            .join(Author)
            .where(Author.id.in_(author_ids))
            .execute())

# a subquery as a computed column: the max views, per article
max_views = (Article.select(fn.MAX(Article.views))
                   .group_by(Article.author))
top = (Article.select(Article.title, max_views.alias("author_max"))
            .join(Author)
            .where(Article.views == max_views)
            .execute())

# .switch(): stop following the FK chain and start the subquery fresh
# the subquery is an Expression - it composes with in_(), ==, and the logic

A subquery is a select used where a value is expected: as the argument to in_() (the "filter to a set defined by another query" case), or as a compared expression. The .switch() call is the piece that is unique to subqueries - a subquery built by continuing a query's join chain would otherwise keep joining onto the outer query's models, and switch() resets the chain so the subquery starts from its own first model. The subquery renders as a nested (SELECT ...) in the SQL, and the database executes it as part of the outer statement - one round trip for what would be two separate queries. The alias form names a computed subquery column so the outer query can reference it. The patterns to recognize: existence filters (in_ a subquery), per-group aggregates in a computed column, and the comparison against an aggregate - all of them are the subquery-as-expression idea with a different operator.

The Working Rules

  • Join when the query filters, orders, or projects on the related data - the condition needs the related columns in the same statement.
  • Prefetch when the loop only displays the relationship - the related data is not part of the filter, so a second query for all of it beats N queries for one each.
  • Use LEFT JOIN when the parent must appear even without the child - an inner join silently drops the rows without a match, which is the "my list got shorter" bug.
  • Subquery for the set-defined-by-a-query filter - in_(subquery) is the parameterized, single-statement form; never fetch the ids into Python and build the list by hand when the database can do it.
  • Print the query. The join's ON, the subquery's nesting, the prefetch's absence - all of them are visible in the SQL before .execute(), and that visibility is the control.

Summary

.join(Model) crosses a relationship in one statement, deriving the ON from the FK, with JOIN.LEFT for the outer variant and an explicit on= for the non-FK join; the filter after the join references either side. The N+1 - one query per row for a relationship accessed in a loop - is fixed by prefetch, which loads all the related rows in a second query and serves the loop from memory. Subqueries are a select used as an expression - in_(subquery) for the set-defined filter, switch() to reset the join chain inside it, alias to name a computed column. The decision rule: join to filter on the related data, prefetch to display it, subquery to define a set - and print(q) is the habit that keeps the generated SQL, with its joins and nesting, visible and checkable before it runs.