PythonSQLAlchemySQLitePostgreSQL

Filtering Relationship Collections

Yonatan Vega20 min read
An alchemist filtering potions from different collections, symbolizing SQLAlchemy relationship filtering.

Let's expand our understanding of relationship loading by exploring how to filter the collections that get loaded. In most real-world scenarios, you don't need all related objects - you only need those that match specific criteria. SQLAlchemy provides powerful ways to filter relationship collections using the same eager loading techniques that we’ve seen in the last chapter.

Let's define our tables

Let's begin by defining tables with relationships that we can use for this course. For these examples, we’re going to need tables with relationships of the following kinds :

  • one-to-many.
  • many-to-one.
  • many-to-many.

Note that since one-to-one relationships are pretty straightforward, they don't require any special treatment when filtering, and many of the examples here will be naturally transferable with one-to-one relationships. 

from sqlalchemy import String, Integer, ForeignKey, Text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from typing import List, Optional
from datetime import datetime

class Base(DeclarativeBase):
  pass

class Location(Base):
  __tablename__ = 'locations'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(100), nullable=False)
  country: Mapped[str] = mapped_column(String(50), nullable=False)
  
  alchemists: Mapped[List["Alchemist"]] = relationship("Alchemist", back_populates="location")

class Alchemist(Base):
  __tablename__ = 'alchemists'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50), nullable=False)
  age: Mapped[Optional[int]] = mapped_column(Integer)
  biography: Mapped[Optional[str]] = mapped_column(Text)
  location_id: Mapped[Optional[int]] = mapped_column(ForeignKey('locations.id'))
  mentor_id: Mapped[Optional[int]] = mapped_column(ForeignKey('alchemists.id'))
  
  # Relationships
  potions: Mapped[List["Potion"]] = relationship("Potion", back_populates="alchemist")
  location: Mapped[Optional["Location"]] = relationship("Location", back_populates="alchemists")
  
  # Self-referential relationship for mentor/apprentice
  mentor: Mapped[Optional["Alchemist"]] = relationship(
      "Alchemist", 
      remote_side=[id], 
      foreign_keys=[mentor_id],
      backref="apprentices"
  )
  

class Potion(Base):
  __tablename__ = 'potions'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50), nullable=False)
  potency: Mapped[Optional[int]] = mapped_column(Integer)
  alchemist_id: Mapped[Optional[int]] = mapped_column(ForeignKey('alchemists.id'))
  
  # Relationships
  alchemist: Mapped[Optional["Alchemist"]] = relationship("Alchemist", back_populates="potions")
  ingredients: Mapped[List["Ingredient"]] = relationship(
      "Ingredient", 
      secondary="potion_ingredient",
      back_populates="potions"
  )
  
  
class Ingredient(Base):
  __tablename__ = 'ingredients'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50), nullable=False)
  
  # Relationships
  potions: Mapped[List["Potion"]] = relationship(
      "Potion", 
      secondary="potion_ingredient",
      back_populates="ingredients"
  )

# Association table as a mapped class
class PotionIngredient(Base):
  __tablename__ = 'potion_ingredient'
  
  potion_id: Mapped[int] = mapped_column(ForeignKey('potions.id'), primary_key=True)
  ingredient_id: Mapped[int] = mapped_column(ForeignKey('ingredients.id'), primary_key=True)
  
  # Optional - add additional data about the relationship
  amount: Mapped[Optional[str]] = mapped_column(String(50))
  
  # Define relationships to both sides if needed
  potion: Mapped["Potion"] = relationship("Potion", viewonly=True)
  ingredient: Mapped["Ingredient"] = relationship("Ingredient", viewonly=True)

Let's populate our tables first

Population script

Before we get into the examples, let's populate our tables so we can start making queries on our relationships

from sqlalchemy import create_engine

# Create an in-memory SQLite database
engine = create_engine("sqlite:///:memory:", echo=True)
Base.metadata.create_all(engine)
from sqlalchemy.orm import Session
import random

with Session(engine) as session:
  # Bulk insert locations
  locations = [
      Location(name="Paris Laboratory", country="France"),
      Location(name="London Workshop", country="England"),
      Location(name="Swiss Mountain Retreat", country="Switzerland"),
      Location(name="Cairo Study", country="Egypt"),
      Location(name="Prague Tower", country="Czech Republic")
  ]
  session.add_all(locations)
  session.flush()  # Flush to get IDs
  
  # Bulk insert alchemists with locations
  alchemists = [
      Alchemist(name="Nicolas Flamel", age=665, location=locations[0], biography="Created the Philosopher's Stone"),
      Alchemist(name="Paracelsus", age=49, location=locations[2], biography="Father of Toxicology"),
      Alchemist(name="Hermes Trismegistus", age=3000, location=locations[3], biography="Founder of Hermeticism"),
      Alchemist(name="Mary the Fearless", age=45, location=locations[0], biography="Invented the double boiler"),
      Alchemist(name="Jabir ibn Hayyan", age=73, location=locations[3], biography="Father of early chemistry"),
      Alchemist(name="Edward Elric", age=16, location=locations[4], biography="Youngest State Alchemist"),
      Alchemist(name="Agrippa", age=51, location=locations[1], biography="Author of Occult Philosophy")
  ]
  session.add_all(alchemists)
  
  # Set up mentor relationships
  alchemists[1].mentor = alchemists[0]  # Paracelsus mentored by Flamel
  alchemists[3].mentor = alchemists[0]  # Mary mentored by Flamel
  alchemists[5].mentor = alchemists[1]  # Edward mentored by Paracelsus
  alchemists[6].mentor = alchemists[2]  # Agrippa mentored by Hermes
  session.flush()
  
  # Bulk insert ingredients
  ingredients = [
      Ingredient(name="Mercury"),
      Ingredient(name="Sulfur"),
      Ingredient(name="Salt"),
      Ingredient(name="Lead"),
      Ingredient(name="Gold"),
      Ingredient(name="Silver"),
      Ingredient(name="Copper"),
      Ingredient(name="Iron"),
      Ingredient(name="Antimony"),
      Ingredient(name="Arsenic"),
      Ingredient(name="Water"),
      Ingredient(name="Dragon's Blood"),
      Ingredient(name="Mandrake Root"),
      Ingredient(name="Moonstone"),
      Ingredient(name="Philosopher's Stone")
  ]
  session.add_all(ingredients)
  session.flush()
  
  # Bulk insert potions
  potions = [
      Potion(name="Elixir of Life", potency=10, alchemist=alchemists[0]),
      Potion(name="Philosopher's Stone Solution", potency=9, alchemist=alchemists[0]),
      Potion(name="Healing Salve", potency=6, alchemist=alchemists[1]),
      Potion(name="Transmutation Catalyst", potency=8, alchemist=alchemists[2]),
      Potion(name="Youth Restoration", potency=7, alchemist=alchemists[3]),
      Potion(name="Wisdom Tincture", potency=5, alchemist=alchemists[2]),
      Potion(name="Elemental Binding", potency=8, alchemist=alchemists[4]),
      Potion(name="Astral Projection", potency=9, alchemist=alchemists[6]),
      Potion(name="Golden Elixir", potency=7, alchemist=alchemists[5]),
      Potion(name="Invisibility Solution", potency=6, alchemist=alchemists[3])
  ]
  session.add_all(potions)
  session.flush()
  
  # Create potion-ingredient associations with amounts
  associations = []
  
  # Add special ingredients to specific potions
  associations.append(PotionIngredient(potion_id=potions[0].id, ingredient_id=ingredients[14].id, amount="1 fragment"))  # Philosopher's Stone in Elixir of Life
  associations.append(PotionIngredient(potion_id=potions[1].id, ingredient_id=ingredients[0].id, amount="100 grams"))    # Mercury in Philosopher's Stone Solution
  associations.append(PotionIngredient(potion_id=potions[1].id, ingredient_id=ingredients[1].id, amount="100 grams"))    # Sulfur in Philosopher's Stone Solution

  for potion in potions:
      # Add 3-5 random ingredients for each potion
      for _ in range(random.randint(3, 5)):
          ingredient = random.choice(ingredients)
          amount = f"{random.randint(1, 10)} {random.choice(['drops', 'grams', 'pieces', 'ounces'])}"
          
          # Avoid duplicate associations
          if not any(a.potion_id == potion.id and a.ingredient_id == ingredient.id for a in associations):
              associations.append(
                  PotionIngredient(
                      potion_id=potion.id, 
                      ingredient_id=ingredient.id,
                      amount=amount
                  )
              )
  
  session.add_all(associations)
  
  # Commit all data
  session.commit()
  
  # Verify data was inserted
  print(f"Inserted {len(locations)} locations")
  print(f"Inserted {len(alchemists)} alchemists")
  print(f"Inserted {len(ingredients)} ingredients")
  print(f"Inserted {len(potions)} potions")
  print(f"Inserted {len(associations)} potion-ingredient associations")

Filtering collections during eager loading

SQLAlchemy allows you to apply filtering criteria directly to your loader options. This is particularly useful when you want to eagerly load only a subset of related objects.

from sqlalchemy import select
from sqlalchemy.orm import selectinload

# Create a new session
with Session(engine) as session:
  # Load alchemists and only their powerful potions (potency > 7)
  stmt = (
      select(Alchemist)
      .options(
          selectinload(
              Alchemist.potions.and_(Potion.potency > 7)
          )
      )
  )
  alchemists = session.scalars(stmt).all()

  # Only powerful potions are loaded
  print("\nAccessing powerful potions (already loaded, no additional query):")
  for alchemist in alchemists:
      print(f"{alchemist.name}'s powerful potions:")
      for potion in alchemist.potions:
          print(f"  - {potion.name} (Potency: {potion.potency})")

  # If we were to access ALL potions, it would trigger a new query
  print("\nNow accessing ALL potions (triggers new queries):")
  for alchemist in alchemists:
      # We can use session.refresh() to reload the relationship without the filter
      session.refresh(alchemist, ["potions"])
      print(f"{alchemist.name}'s all potions:")
      for potion in alchemist.potions:
          print(f"  - {potion.name} (Potency: {potion.potency})")

In this example, we're using selectinload with a filtering criterion to load only potions with a potency greater than 7. When we access alchemist.potions, we only get these high-potency potions without triggering additional queries.

You might be tempted to attempt filtering relationship collections by applying filters on the relationships on the outer query, but this actually does not work, to filter the collection you must either apply filters on the relationships, or as you will see later in the article, use joins to filter both the relationships and the outer query results.

🧙‍♂️ Up to this point, we’ve filtered the collections we get through our relationship loading. You should know that this is not the way to filter the primary objects based on their relationships; we explore that topic later in this chapter.

Filtering nested relationships

You can also apply filters to nested relationships in a chain:

# Create a new session
with Session(engine) as session:
  # Load alchemists with their potions, but only include
  # potions that contain a specific ingredient
  stmt = (
      select(Alchemist)
      .options(
          selectinload(
              Alchemist.potions.and_(
                  Potion.ingredients.any(Ingredient.name == "Mercury")
              )
          )
      )
  )
  alchemists = session.scalars(stmt).all()

  # Only potions with Mercury are loaded
  print("\nPotions containing Mercury:")
  for alchemist in alchemists:
      print(f"{alchemist.name}'s mercury-based potions:")
      for potion in alchemist.potions:
          print(f"  - {potion.name}")

In this example, we're filtering options to include only those that contain an ingredient named "Mercury.” The any() method is used to filter many-to-many relationships.

🧙‍♂️ The any() is great for filtering based on one-to-many and many-to-many relationships. It allows you to filter based on the relationships by asking for objects whose related objects pass some criteria.

Filtering Based on Relationships

In addition to filtering the collections that get loaded, you can also filter the primary objects based on conditions applied to their relationships. This is a powerful technique for finding objects based on the properties of their related objects.

Different filtering operators for collections

SQLAlchemy provides various operators for filtering collections:

with Session(engine) as session:
  # 1. Using contains() - load alchemists who have a specific potion
  stmt1 = (
      select(Alchemist)
      .where(Alchemist.potions.contains(
          session.execute(select(Potion).where(Potion.name == "Elixir of Life")).scalar()
      ))
  )
  philosophers = session.scalars(stmt1).all()
  print(f"Alchemists who created the Elixir of Life: {[a.name for a in philosophers]}")

  # 2. Using any() - load alchemists who have any potion with potency > 8
  stmt2 = (
      select(Alchemist)
      .where(Alchemist.potions.any(Potion.potency > 8))
  )
  powerful_alchemists = session.scalars(stmt2).all()
  print(f"Alchemists with powerful potions: {[a.name for a in powerful_alchemists]}")

  # 3. Using has() - load potions that have a specific alchemist
  stmt3 = (
      select(Potion)
      .where(Potion.alchemist.has(Alchemist.name == "Nicolas Flamel"))
  )
  flamel_potions = session.scalars(stmt3).all()
  print(f"Flamel's potions: {[p.name for p in flamel_potions]}")

This example demonstrates three common operators for filtering collections:

  • contains() checks if a collection contains a specific object
    • this filtering method is only valid for collection-type relationships, i.e., one-to-many and many-to-many relationships.
  • any() checks if any item in a collection matches a criterion
    • Used when a relationship is a collection such as a list, like in one-to-many and many-to-many relationships
    • This filtering method uses a correlated subquery using EXISTS
  • has() checks if a scalar relationship matches a criterion
    • This filtering method only works for scalar relationships, i.e. relationships that are not collections types, such as one-to-one and many-to-one relationships.
    • This method also uses a correlated subquery using EXISTS

These operators provide a powerful way to filter your queries based on relationships.

Filtering primary objects based on relationship criteria

with Session(engine) as session:

  # Find alchemists who have created potions with potency > 8
  stmt = (
      select(Alchemist)
      .join(Alchemist.potions)
      .where(Potion.potency > 8)
  )
  powerful_alchemists = session.scalars(stmt).unique().all()

  print("Alchemists with powerful potions:")
  for alchemist in powerful_alchemists:
      print(f"- {alchemist.name}")

  # Find alchemists who have created potions with specific ingredients
  stmt = (
      select(Alchemist)
      .join(Alchemist.potions)
      .join(Potion.ingredients)
      .where(Ingredient.name == "Mercury")
  )
  mercury_alchemists = session.scalars(stmt).unique().all()

  print("Alchemists who work with Mercury:")
  for alchemist in mercury_alchemists:
      print(f"- {alchemist.name}")

In these examples, we join from the Alchmist object table and it’s related tables and then apply filters on those related tables. This finds alchemists based on properties of their related objects.

🧙‍♂️ Even though we are not using relationship loaders such selectinload or joinedload etc.. we can still use our relationships using regular join statements, this actually makes a lot of sense, we are not required to load relationships if we don’t really need them.

Notice the use of unique() to ensure we don't get duplicate alchemists when a single alchemist has multiple potions that match our criteria.

Using exists() subqueries for filtering

Another approach is to use exists() subqueries, which can sometimes be more efficient:

from sqlalchemy import exists

with Session(engine) as session:
  session.bind.echo = True

  # Find alchemists who have created potions with potency > 8
  # using exists() subquery
  stmt = (
      select(Alchemist)
      .where(
          exists()
          .where(Potion.alchemist_id == Alchemist.id)
          .where(Potion.potency > 8)
      )
  )
  powerful_alchemists = session.scalars(stmt).all()
  print("---")
  print("Alchemists with powerful potions (using exists):")
  for alchemist in powerful_alchemists:
      print(f"- {alchemist.name}")

  # Find alchemists who have created potions with specific ingredients
  # using nested exists() subqueries
  stmt = (
      select(Alchemist)
      .where(
          exists()
          .where(Potion.alchemist_id == Alchemist.id)
          .where(
              exists()
              .where(PotionIngredient.potion_id == Potion.id)
              .where(PotionIngredient.ingredient_id == Ingredient.id)
              .where(Ingredient.name == "Mercury")
          )
      )
  )
  mercury_alchemists = session.scalars(stmt).all()
  print("---")
  print("Alchemists who work with Mercury (using exists):")
  for alchemist in mercury_alchemists:
      print(f"- {alchemist.name}")

The exists() subquery approach can be more efficient for large datasets, especially when you only need to check for the existence of related objects and don't need to load them.

Combining filtering and eager loading

One of the most powerful techniques is to combine filtering based on relationships with eager loading:

with Session(engine) as session:
  session.bind.echo = True

  # Find alchemists who have created potions with potency > 6
  # AND eagerly load all their potions
  stmt = (
      select(Alchemist)
      .join(Alchemist.potions)
      .where(Potion.potency > 6)
      .options(selectinload(Alchemist.potions))
  )
  result = session.execute(stmt)
  powerful_alchemists = result.unique().scalars().all()

  print("Alchemists with potent potions along with the rest of their potions:")
  for alchemist in powerful_alchemists:
      print(f"{alchemist.name}'s potions:")
      for potion in alchemist.potions:
          print(f"  - {potion.name} (Potency: {potion.potency})")

This example combines filtering (finding alchemists with potent potions) with eager loading (loading all their potions, not just the powerful ones).

Note that:

  1. We use .join(Alchemist.potions) and .filter(Potion.potency > 6) to find alchemists with powerful potions
  2. We use .options(selectinload(Alchemist.potions)) to eagerly load ALL potions for these alchemists
  3. We use .unique() to ensure we don't get duplicate alchemists

This pattern is extremely useful in real-world applications where you need to:

  1. Find entities that match complex criteria
  2. Load all of their related data for display or processing

Enhanced Joinedload Examples

Let's dive deeper into joinedload() with more complex examples and scenarios.

Multi-level joinedload with filtering

The joinedload() strategy can be applied to multiple levels of relationships:

from sqlalchemy.orm import joinedload

# Create a new session
with Session(engine) as session:
  session.bind.echo = True

  print("Multi-level joinedload with filtering:")

  # Find potions with high potency and load their alchemist and the alchemist's location
  stmt = (
      select(Potion)
      .where(Potion.potency > 5)
      .options(
          joinedload(Potion.alchemist)
          .joinedload(Alchemist.location)
      )
  )
  result = session.execute(stmt)
  potions = result.scalars().all()

  print("Potent potions with their creators and locations:")
  for potion in potions:
      location_name = potion.alchemist.location.name if potion.alchemist and potion.alchemist.location else "Unknown"
      print(f"{potion.name} (Potency: {potion.potency}) created by {potion.alchemist.name} at {location_name}")

In this example, we load potions, their alchemists, and the alchemists' locations in a single query using chained joinedloads. This avoids the need for additional queries when accessing these nested relationships.

🧙‍♂️ As you may know, we can actually access these fields without the joinedload statements, the difference is that when we use loaders, we actually avoid eager loading.

Combining different loading strategies

You can combine joinedload() with other loading strategies to optimize your queries:

from sqlalchemy.orm import joinedload, selectinload

# Create a new session
with Session(engine) as session:
  session.bind.echo = True

  # Load potions with their alchemist (many-to-one) using joinedload
  # and ingredients (many-to-many) using selectinload
  stmt = (
      select(Potion)
      .options(
          joinedload(Potion.alchemist),  # Good for many-to-one
          selectinload(Potion.ingredients)  # Better for collections
      )
  )
  result = session.execute(stmt)
  potions = result.scalars().all()

  print("Potions with their creators and ingredients:")
  for potion in potions:
      print(f"{potion.name} created by {potion.alchemist.name}")
      print("  Ingredients:")
      for ingredient in potion.ingredients:
          print(f"    - {ingredient.name}")

This approach uses the most efficient loading strategy for each relationship type:

  • joinedload for many-to-one relationships (Potion to Alchemist)
  • selectinload for collections (Potion to Ingredients)

The reason why this is a more “optimized” solution is due to the fact that each potion is only associated with one alchemist, meaning that

  • When joining using joinedload we get a single row per potion and alchemist, meaning no duplicate rows (since we only have an association to one alchemist)
  • Ingredients are a one to many relationship from the side of Potion meaning that if we were to join Potion on Ingredient we would actually get a duplicate Potion row per Ingredient, while SQLAlchemy would provide us with the objects correctly, without duplication, the underlying query is less optimized than a straightforward select of ingredients that selectinload provides

🧙‍♂️ When optimizing queries, remember that behind the scenes SQLAlchemy constructs queries and translates them to objects, in the case that your queries are unoptimized you might find your code runs slower, or database queries take longer, in this case it makes sense to look at how you use relationship loaders.

The JOIN + contains_eager technique

One powerful pattern in SQLAlchemy is combining explicit JOINs for filtering with contains_eager for populating relationships. This technique allows you to filter both the primary objects and their related collections in a single query.

from sqlalchemy.orm import contains_eager

# Create a new session
with Session(engine) as session:
  session.bind.echo = True

  print("\nThe JOIN + contains_eager technique:")

  # Find alchemists who have created potent potions
  # AND load only those potent potions
  stmt = (
      select(Alchemist)
      .join(Alchemist.potions)  # Join to potions for filtering
      .where(Potion.potency > 7)  # Filter for potent potions
      .options(contains_eager(Alchemist.potions))  # Use the JOIN data for the relationship
  )
  result = session.execute(stmt)
  alchemists = result.unique().scalars().all()

  print("\nAlchemists and their potent potions:")
  for alchemist in alchemists:
      print(f"{alchemist.name}'s potent potions:")
      # This will only show potions with potency > 7
      for potion in alchemist.potions:
          print(f"  - {potion.name} (Potency: {potion.potency})")

In this example:

  1. We use .join(Alchemist.potions) to join to the potions table
  2. We use .filter(Potion.potency > 7) to filter for potent potions
  3. We use .options(contains_eager(Alchemist.potions)) to tell SQLAlchemy to use the joined data to populate the potions relationship
  4. The result is that alchemist.potions only contains potions with potency > 7

🧙‍♂️ If you go back to the previous article about Relationship Loading Techniques, you can refresh you memory about how the contains_eager loader works, in essence, it instructs SQLAlchemy to populate the relationship based on the loaded columns from the base query.

Since we join the Potion object in the base query, we can use this loader and get a populated collection of Potion objects get pulled from the query, in this case our collection naturally gets filtered based on the potency column filter.

This differs from our earlier selectinload with filtering example. Let's compare them:

# Create a new session
with Session(engine) as session:
  session.bind.echo = False

  print("\nComparison of filtering techniques:")

  # Approach 1: JOIN + contains_eager
  stmt1 = (
      select(Alchemist)
      .join(Alchemist.potions)
      .where(Potion.potency > 7)
      .options(contains_eager(Alchemist.potions))
  )
  result = session.execute(stmt1)
  alchemists1 = result.unique().scalars().all()
  session.expunge_all()  # Clear the session to avoid caching issues

  # Approach 2: Filter alchemists + selectinload with filter
  stmt2 = (
      select(Alchemist)
      .join(Alchemist.potions)
      .where(Potion.potency > 7)
      .options(selectinload(Alchemist.potions.and_(Potion.potency > 7)))
  )
  result = session.execute(stmt2)
  alchemists2 = result.unique().scalars().all()
  
  session.expunge_all() # Clear the session to avoid caching issues
  # Approach 3: Filter alchemists + load ALL potions
  stmt3 = (
      select(Alchemist)
      .join(Alchemist.potions)
      .where(Potion.potency > 7)
      .options(selectinload(Alchemist.potions))
  )
  result = session.execute(stmt3)
  alchemists3 = result.unique().scalars().all()

  # Compare the results
  print("\nApproach 1 (JOIN + contains_eager):")
  for alchemist in alchemists1:
      print(f"{alchemist.name}'s potions: {[f"{p.name} - ({p.potency})" for p in alchemist.potions]}")

  print("\nApproach 2 (selectinload with filter):")
  for alchemist in alchemists2:
      print(f"{alchemist.name}'s potions: {[f"{p.name} - ({p.potency})" for p in alchemist.potions]}")

  print("\nApproach 3 (selectinload without filter):")
  for alchemist in alchemists3:
      print(f"{alchemist.name}'s potions: {[f"{p.name} - ({p.potency})" for p in alchemist.potions]}")

The key differences:

  1. Approach 1 (JOIN + contains_eager): One query, filters both alchemists and potions
  2. Approach 2 (selectinload with filter): Two queries, filters both alchemists and potions
  3. Approach 3 (selectinload without filter): Two queries, filters alchemists but loads ALL potions

So when should you use each approach?

  • Use JOIN + contains_eager when:
    • You need to filter both the primary objects and their related collections
    • You want to minimize the number of queries
    • You need precise control over the join conditions
  • Use selectinload with filter when:
    • You want to load primary objects first, then related objects
    • You have a large dataset where joins might be inefficient
    • You want the queries to be more readable and maintainable
  • Use selectinload without filter when:
    • You need to filter primary objects based on relationships but still need all related objects
    • You want to show "these alchemists have powerful potions, here are all their potions"

🧙‍♂️ Note that between each session.execute call we are also calling expunge_all which will clear all of the transient objects from the session and “release” the objects from the cache.

The problem that we are avoiding is the within the same session, by default, SQLAlchemy will attempt to cache results sets and provide you with results from previous queries, since we already hold the Alchemist objects in the session, SQLAlchemy does not hydrate the Potion relationship collection, which provides us with the wrong result set.

If you would like to test this, remove the expunge_all function between the second and third statements, and you would get the same result set of the second query in the third.

To avoid this, you may use different sessions, use the expunge_all function (less recommended) or use the .execution_options(*populate_existing*=True) on the select statement or as part of the execute function.

Note that populate_existing will also update your already loaded objects, which can be very helpful if you would like to repopulate your objects later in a program.

Using aliased entities with joinedload

When working with self-referential relationships or joining to the same table multiple times, you may need to use aliased entities with joinedload

When working with SQLAlchemy, you'll eventually encounter situations where you need to reference the same table multiple times in a single query. This is where aliased entities come into play.

Without aliases, when we try to query both the primary object and the self referential object in a single query, SQLAlchemy wouldn't be able to tell which instance of the table is which. The aliases give a distinct reference point for each occurrence of the table, letting us build clear and unambiguous joins.

Let begin by creating a new Table declaration

from sqlalchemy import ForeignKey, Integer, String
from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship, Mapped
from typing import Optional, List

# Define our base class
class GuildBase(DeclarativeBase):
  pass

# Define our Guild model with self-referential relationship
class Guild(GuildBase):
  __tablename__ = 'guilds'
  
  id: Mapped[int] = mapped_column(primary_key=True)
  name: Mapped[str] = mapped_column(String(50), nullable=False)
  location: Mapped[str] = mapped_column(String(100))
  founding_year: Mapped[int] = mapped_column(Integer)
  
  # Self-referential relationship - a guild can have a parent guild
  parent_id: Mapped[Optional[int]] = mapped_column(ForeignKey("guilds.id"))
  
  # A parent guild can have many child guilds (branches)
  branches: Mapped[List["Guild"]] = relationship(
      back_populates="parent", 
      foreign_keys=[parent_id]
  )
  
  # Each guild has at most one parent guild
  parent: Mapped[Optional["Guild"]] = relationship(
      back_populates="branches",
      remote_side=[id]
  )

GuildBase.metadata.create_all(engine)

🧙‍♂️ Note that we are mapping our self referential relationships using the “stringified” name of our class, the reason is that the class is not declared yet when we assign it to itself!

Thankfully SQLAlchemy supports this kind of mapper assignment due to exactly this problem, where our models might not be registered in memory at the time of their usage, and using the Declarative base and the underlying registry is able to allocate the correct models after all of our models have been registered.

Next, let populate our guilds table

from sqlalchemy.orm import Session

with Session(engine) as session:
  # Create main guilds
  grand_alchemical = Guild(
      name="Grand Alchemical Society", 
      location="London", 
      founding_year=1245
  )
  
  royal_transmutation = Guild(
      name="Royal Transmutation Order", 
      location="Paris", 
      founding_year=1302
  )
  
  # Create branch guilds
  metallurgists = Guild(
      name="Metallurgists Brotherhood", 
      location="Birmingham", 
      founding_year=1410,
      parent=grand_alchemical
  )
  
  herbal_essence = Guild(
      name="Herbal Essence Coalition", 
      location="Provence", 
      founding_year=1455,
      parent=royal_transmutation
  )
  
  philosophers = Guild(
      name="Philosophers Circle", 
      location="Oxford", 
      founding_year=1522,
      parent=grand_alchemical
  )
  
  # Third level guild
  iron_workers = Guild(
      name="Iron Workers Collective", 
      location="Sheffield", 
      founding_year=1612,
      parent=metallurgists
  )
  
  session.add_all([
      grand_alchemical, royal_transmutation, 
      metallurgists, herbal_essence, philosophers,
      iron_workers
  ])
  session.commit()

And finally to actually query our guilds table we can do the following

from sqlalchemy import select
from sqlalchemy.orm import aliased, joinedload

with Session(engine) as session:
  # Create aliases for the Guild class to represent different hierarchy levels
  ParentGuild = aliased(Guild)
  GrandparentGuild = aliased(Guild)
  
  # Load guilds with their parent and grandparent guilds in a single query
  # using the aliases to differentiate between levels
  stmt = (
      select(Guild)
      .options(
          joinedload(Guild.parent.of_type(ParentGuild))
          .joinedload(ParentGuild.parent.of_type(GrandparentGuild))
      )
  )
  
  guilds = session.scalars(stmt).all()
  
  for guild in guilds:
      parent_name = guild.parent.name if guild.parent else "None"
      parent_location = guild.parent.location if guild.parent else "N/A"
      
      grandparent_name = "None"
      grandparent_location = "N/A"
      if guild.parent and guild.parent.parent:
          grandparent_name = guild.parent.parent.name
          grandparent_location = guild.parent.parent.location
      
      print(f"Guild: {guild.name} (Founded: {guild.founding_year} in {guild.location})")
      print(f"  Parent Guild: {parent_name} (Location: {parent_location})")
      print(f"  Grandparent Guild: {grandparent_name} (Location: {grandparent_location})")
      print("---")

This example demonstrates how to use aliases with joinedload to handle self-referential relationships and multiple joins to the same table.

🧙‍♂️ As we can see, aliases are also incredibly useful when joining the same table multiple times through different relationships. In our example we actually use aliases to create a self referential relationship between a Guild object and its GrandparentGuild through a ParentGuild, aliases help SQLAlchemy distinguish between these two relationships in your queries.

Optimizing Relationship Loading for Complex Queries

Let's apply everything we've learned to optimize a complex real-world query scenario:

Scenario

We need to find alchemists who:

  1. Are located in a specific country
  2. Have created potions with potency > 7
  3. Have used Mercury in at least one potion

And we need to load:

  1. The alchemist's location
  2. All of their potions (not just the filtered ones)
  3. The ingredients for each potion

Unoptimized Query

# Create a new session
from datetime import datetime, timedelta

with Session(engine) as session:
  session.bind.echo = True

  # 1. Find alchemists in France who have created potent potions with Mercury
  alchemists = (
      session.query(Alchemist)
      .join(Alchemist.location)
      .where(Location.country == "France")
      .where(
          exists()
          .where(Potion.alchemist_id == Alchemist.id)
          .where(Potion.potency > 7)
      )
      .filter(
          select(Potion)
          .join(PotionIngredient)
          .join(Ingredient)
          .where(Potion.alchemist_id == Alchemist.id)
          .where(Ingredient.name == "Mercury")
          .exists()
      )
      .all()
  )

  # 2. For each alchemist, load their location, potions, and ingredients (N+1 problem)
  for alchemist in alchemists:
      print(f"\n{alchemist.name} (Location: {alchemist.location.name}, {alchemist.location.country})")
      print("Potions:")
      for potion in alchemist.potions:
          print(f"  - {potion.name} (Potency: {potion.potency})")
          print("    Ingredients:")
          for ingredient in potion.ingredients:
              print(f"      * {ingredient.name}")

Optimized Query

with Session(engine) as session:
  session.bind.echo = True
  # This complex query does everything we need in 4 queries:
  stmt = (
      select(Alchemist)
      # Join for filtering
      .join(Alchemist.location)
      .filter(Location.country == "France")
      # Subqueries for the complex filtering conditions
      .filter(
          exists()
          .where(Potion.alchemist_id == Alchemist.id)
          .where(Potion.potency > 7)
      )
      .filter(
          select(Potion)
          .join(PotionIngredient)
          .join(Ingredient)
          .where(Potion.alchemist_id == Alchemist.id)
          .where(Ingredient.name == "Mercury")
          .exists()
      )
      # Eager loading for the data we need
      .options(
          joinedload(Alchemist.location),  # Load location (many-to-one)
          selectinload(Alchemist.potions)  # Load all potions (one-to-many)
          .selectinload(Potion.ingredients)  # Load ingredients (many-to-many)
      )
  )

  result = session.execute(stmt)
  alchemists = result.unique().scalars().all()

  # Now we can access all the data without additional queries
  for alchemist in alchemists:
      print(f"\n{alchemist.name} (Location: {alchemist.location.name}, {alchemist.location.country})")
      print("Potions:")
      for potion in alchemist.potions:
          print(f"  - {potion.name} (Potency: {potion.potency})")
          print("    Ingredients:")
          for ingredient in potion.ingredients:
              print(f"      * {ingredient.name}")

This complex example combines many of the techniques we've discussed:

  1. Explicit joins for filtering
  2. Subqueries with exists() for complex conditions
  3. Different loading strategies for different relationship types
  4. The unique() method to prevent duplicates

The optimized approach:

  • Uses complex filtering to find the right alchemists
  • Loads their location with joinedload (efficient for many-to-one)
  • Loads all their potions with selectinload (efficient for collections)
  • Loads the ingredients with nested selectinload (efficient for collections)

This pattern scales well to real-world applications with complex data models and query requirements.

Using relationship loading techniques we also avoid the N+1 problem, which means that for every row that we access, we create another query to the database, by making sure we use loaders and carefully choose which loaders we use for each situation, we can make sure that our queries are optimized and our application runs smoothly.

Summary

By mastering these techniques, you can write SQLAlchemy code that is both powerful and efficient, handling complex data relationships with ease while maintaining good performance.

Remember that the best approach depends on your specific use case. Always consider the cardinality of relationships, the size of your dataset, and the access patterns of your application when choosing filtering and loading strategies.

Author image
I'm Yonatan Vega
Owner & Creator, Fullstack.rocks
I've enjoyed writing code ever since I discovered it was an option. At a young age I started by writing code for MMORPG's private servers, and hosting my own private servers at home.

Today I'm writing and architecting software using many different technologies, and I'm always looking for the next thing to learn.