Multiple Relationships to the Same Model¶
We've seen how tables are related to each other via a single relationship attribute but what if more than one attribute links to the same table?
What if you have a User model and an Address model and would like
to have User.home_address and User.work_address relationships to the same
Address model? In SQL you do this one of two ways: 1) by creating a table alias using AS or 2)
by using a correlated sub-query.
Query: Find users with home address zipcode "100000" and work address zipcode = "10001":
Alias Query¶
Using an alias, JOIN the address table twice:
SELECT *
FROM user
JOIN address AS home_address_alias
ON user.home_address_id == home_address_alias.id
JOIN address AS work_address_alias
ON user.work_address_id == work_address_alias.id
WHERE
home_address_alias.zipcode == "10000"
AND work_address_alias.zipcode == "10001"
Correlated Sub-Query¶
Using sub-queries, filter the matches with EXISTS:
SELECT *
FROM user
WHERE (
EXISTS (
SELECT 1 FROM address
WHERE
address.id = user.home_address_id
AND address.zipcode = "10000"
)
) AND (
EXISTS (
SELECT 1 FROM address
WHERE
address.id = user.work_address_id
AND address.zipcode = "10001"
)
Key differences¶
Duplicates: JOIN (alias query) can produce them, EXISTS will not. The duplicates will be removed by the ORM as rows are marshalled into objects.
Performance: Both can be optimized similarly, but JOIN often wins when you’re retrieving columns from the related table.
Readability: JOIN reads like “combine these tables.” EXISTS reads like “filter by a condition.”
✅ Rule of thumb:
If you need columns from the foreign table → use JOIN. For example, if you are using lazy=joined or selectin you may prefer this.
If you only care whether a row exists in the foreign table → use EXISTS.
If the foreign table search criteria (address.zipcode) is not unique, prefer EXISTS unless you also want the duplicates.
The Relationships¶
Let's define a winter_team and summer_team relationship for our heros. They can be on different
winter and summer teams or on the same team for both seasons.
# Code above omitted 👆
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
winter_team_id: Optional[int] = Field(default=None, foreign_key="team.id")
winter_team: Optional[Team] = Relationship(
sa_relationship_kwargs={"foreign_keys": "Hero.winter_team_id"}
)
summer_team_id: Optional[int] = Field(default=None, foreign_key="team.id")
summer_team: Optional[Team] = Relationship(
sa_relationship_kwargs={"foreign_keys": "Hero.summer_team_id"}
)
# Code below omitted 👇
The sa_relationship_kwargs={"foreign_keys": ...} is a new bit of info we need for SQLAlchemy to
figure out which SQL join we should use depending on which attribute is in our query.
Creating Heros¶
Creating Heros with the multiple teams is no different from before. We set the same or different
team to the winter_team and summer_team attributes:
# Code above omitted 👆
def create_heroes():
with Session(engine) as session:
team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
team_z_force = Team(name="Z-Force", headquarters="Sister Margaret's Bar")
hero_deadpond = Hero(
name="Deadpond",
secret_name="Dive Wilson",
winter_team=team_preventers,
summer_team=team_z_force,
)
hero_rusty_man = Hero(
name="Rusty-Man",
secret_name="Tommy Sharp",
age=48,
winter_team=team_preventers,
summer_team=team_preventers,
)
session.add(hero_deadpond)
session.add(hero_rusty_man)
session.commit()
session.refresh(hero_deadpond)
session.refresh(hero_rusty_man)
print("Created hero:", hero_deadpond)
print("Created hero:", hero_rusty_man)
# Code below omitted 👇
👀 Full file preview
from typing import Optional
from sqlalchemy.orm import aliased
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine, select
class Team(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
headquarters: str
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
winter_team_id: Optional[int] = Field(default=None, foreign_key="team.id")
winter_team: Optional[Team] = Relationship(
sa_relationship_kwargs={"foreign_keys": "Hero.winter_team_id"}
)
summer_team_id: Optional[int] = Field(default=None, foreign_key="team.id")
summer_team: Optional[Team] = Relationship(
sa_relationship_kwargs={"foreign_keys": "Hero.summer_team_id"}
)
sqlite_file_name = ":memory:"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
with Session(engine) as session:
team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
team_z_force = Team(name="Z-Force", headquarters="Sister Margaret's Bar")
hero_deadpond = Hero(
name="Deadpond",
secret_name="Dive Wilson",
winter_team=team_preventers,
summer_team=team_z_force,
)
hero_rusty_man = Hero(
name="Rusty-Man",
secret_name="Tommy Sharp",
age=48,
winter_team=team_preventers,
summer_team=team_preventers,
)
session.add(hero_deadpond)
session.add(hero_rusty_man)
session.commit()
session.refresh(hero_deadpond)
session.refresh(hero_rusty_man)
print("Created hero:", hero_deadpond)
print("Created hero:", hero_rusty_man)
def select_heroes():
with Session(engine) as session:
winter_alias = aliased(Team)
# Heros with winter team as the Preventers using "aliases" and "onclause"
result = session.exec(
select(Hero)
.join(winter_alias, onclause=Hero.winter_team_id == winter_alias.id)
.where(winter_alias.name == "Preventers")
)
"""
SQL Looks like:
SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.winter_team_id, hero.summer_team_id
FROM hero JOIN team AS team_1 ON hero.winter_team_id = team_1.id
WHERE team_1.name = ?
"""
heros = result.all()
print("Heros with Preventers as their winter team:")
for hero in heros:
print(
f"Hero: {hero.name}, Winter Team: {hero.winter_team.name} Summer Team: {hero.summer_team.name}"
)
# Heros with Preventers as their winter team and Z-Force as their summer team using "has" function.
result = session.exec(
select(Hero)
.where(Hero.winter_team.has(Team.name == "Preventers"))
.where(Hero.summer_team.has(Team.name == "Z-Force"))
)
"""
SQL Looks like:
SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.winter_team_id, hero.summer_team_id
FROM hero
WHERE (
EXISTS (
SELECT 1 FROM team
WHERE team.id = hero.winter_team_id AND team.name = ?
)
) AND (
EXISTS (
SELECT 1 FROM team
WHERE team.id = hero.summer_team_id AND team.name = ?
)
)
"""
heros = result.all()
print(
"Heros with Preventers as their winter and Z-Force as their summer team:",
)
for hero in heros:
print(
f"Hero: {hero.name}, Winter Team: {hero.winter_team.name} Summer Team: {hero.summer_team.name}"
)
assert heros[0].name == "Deadpond"
def main():
create_db_and_tables()
create_heroes()
select_heroes()
if __name__ == "__main__":
main()
Searching for Heros¶
Querying Heros based on the winter or summer teams adds a bit of complication. As
mentioned above, we can solve this with an aliased join or correlated subquery.
Alias Join¶
To use the alias method we need to: 1) create the alias(es) and 2) provide the join in our query.
Aliases¶
We create the alias using sqlalchemy.orm.aliased function and use the alias in the where function. We also
need to provide an onclause argument to the join.
The aliases we create are home_address_alias and work_address_alias. You can think of them
as a view to the same underlying address table. We can do this with SQLModel and SQLAlchemy using sqlalchemy.orm.aliased
and a couple of extra bits of info in our SQLModel join statements.
# Code above omitted 👆
with Session(engine) as session:
winter_alias = aliased(Team)
# Code below omitted 👇
Join¶
Query Heros filtering by Team attributes by manually specifying the join with an onclause to tell SQLAlchemy to join the hero and team tables.
# Code above omitted 👆
with Session(engine) as session:
winter_alias = aliased(Team)
# Heros with winter team as the Preventers using "aliases" and "onclause"
result = session.exec(
select(Hero)
.join(winter_alias, onclause=Hero.winter_team_id == winter_alias.id)
.where(winter_alias.name == "Preventers")
)
"""
SQL Looks like:
SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.winter_team_id, hero.summer_team_id
FROM hero JOIN team AS team_1 ON hero.winter_team_id = team_1.id
WHERE team_1.name = ?
"""
heros = result.all()
print("Heros with Preventers as their winter team:")
for hero in heros:
print(
# Code below omitted 👇
The value for the onclause is the join using the same foreign key
when the relationship is defined in the Hero model.
To use both team attributes in a query, create another alias and add the join.
For more information see SQLAlchemy: Handling Multiple Join Paths.
👀 Full file preview
from typing import Optional
from sqlalchemy.orm import aliased
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine, select
class Team(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
headquarters: str
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
winter_team_id: Optional[int] = Field(default=None, foreign_key="team.id")
winter_team: Optional[Team] = Relationship(
sa_relationship_kwargs={"foreign_keys": "Hero.winter_team_id"}
)
summer_team_id: Optional[int] = Field(default=None, foreign_key="team.id")
summer_team: Optional[Team] = Relationship(
sa_relationship_kwargs={"foreign_keys": "Hero.summer_team_id"}
)
sqlite_file_name = ":memory:"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
with Session(engine) as session:
team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
team_z_force = Team(name="Z-Force", headquarters="Sister Margaret's Bar")
hero_deadpond = Hero(
name="Deadpond",
secret_name="Dive Wilson",
winter_team=team_preventers,
summer_team=team_z_force,
)
hero_rusty_man = Hero(
name="Rusty-Man",
secret_name="Tommy Sharp",
age=48,
winter_team=team_preventers,
summer_team=team_preventers,
)
session.add(hero_deadpond)
session.add(hero_rusty_man)
session.commit()
session.refresh(hero_deadpond)
session.refresh(hero_rusty_man)
print("Created hero:", hero_deadpond)
print("Created hero:", hero_rusty_man)
def select_heroes():
with Session(engine) as session:
winter_alias = aliased(Team)
# Heros with winter team as the Preventers using "aliases" and "onclause"
result = session.exec(
select(Hero)
.join(winter_alias, onclause=Hero.winter_team_id == winter_alias.id)
.where(winter_alias.name == "Preventers")
)
"""
SQL Looks like:
SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.winter_team_id, hero.summer_team_id
FROM hero JOIN team AS team_1 ON hero.winter_team_id = team_1.id
WHERE team_1.name = ?
"""
heros = result.all()
print("Heros with Preventers as their winter team:")
for hero in heros:
print(
f"Hero: {hero.name}, Winter Team: {hero.winter_team.name} Summer Team: {hero.summer_team.name}"
)
# Heros with Preventers as their winter team and Z-Force as their summer team using "has" function.
result = session.exec(
select(Hero)
.where(Hero.winter_team.has(Team.name == "Preventers"))
.where(Hero.summer_team.has(Team.name == "Z-Force"))
)
"""
SQL Looks like:
SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.winter_team_id, hero.summer_team_id
FROM hero
WHERE (
EXISTS (
SELECT 1 FROM team
WHERE team.id = hero.winter_team_id AND team.name = ?
)
) AND (
EXISTS (
SELECT 1 FROM team
WHERE team.id = hero.summer_team_id AND team.name = ?
)
)
"""
heros = result.all()
print(
"Heros with Preventers as their winter and Z-Force as their summer team:",
)
for hero in heros:
print(
f"Hero: {hero.name}, Winter Team: {hero.winter_team.name} Summer Team: {hero.summer_team.name}"
)
assert heros[0].name == "Deadpond"
def main():
create_db_and_tables()
create_heroes()
select_heroes()
if __name__ == "__main__":
main()
Correlated Sub Query¶
From a query perspecitve, this is a much simpler solution. We use the has function in the query:
# Code above omitted 👆
# Heros with Preventers as their winter team and Z-Force as their summer team using "has" function.
result = session.exec(
select(Hero)
.where(Hero.winter_team.has(Team.name == "Preventers"))
.where(Hero.summer_team.has(Team.name == "Z-Force"))
)
"""
SQL Looks like:
SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.winter_team_id, hero.summer_team_id
FROM hero
WHERE (
EXISTS (
SELECT 1 FROM team
WHERE team.id = hero.winter_team_id AND team.name = ?
)
) AND (
EXISTS (
SELECT 1 FROM team
WHERE team.id = hero.summer_team_id AND team.name = ?
)
)
"""
heros = result.all()
print(
"Heros with Preventers as their winter and Z-Force as their summer team:",
)
for hero in heros:
print(
f"Hero: {hero.name}, Winter Team: {hero.winter_team.name} Summer Team: {hero.summer_team.name}"
)
# Code below omitted 👇
👀 Full file preview
from typing import Optional
from sqlalchemy.orm import aliased
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine, select
class Team(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
headquarters: str
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: Optional[int] = Field(default=None, index=True)
winter_team_id: Optional[int] = Field(default=None, foreign_key="team.id")
winter_team: Optional[Team] = Relationship(
sa_relationship_kwargs={"foreign_keys": "Hero.winter_team_id"}
)
summer_team_id: Optional[int] = Field(default=None, foreign_key="team.id")
summer_team: Optional[Team] = Relationship(
sa_relationship_kwargs={"foreign_keys": "Hero.summer_team_id"}
)
sqlite_file_name = ":memory:"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
with Session(engine) as session:
team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
team_z_force = Team(name="Z-Force", headquarters="Sister Margaret's Bar")
hero_deadpond = Hero(
name="Deadpond",
secret_name="Dive Wilson",
winter_team=team_preventers,
summer_team=team_z_force,
)
hero_rusty_man = Hero(
name="Rusty-Man",
secret_name="Tommy Sharp",
age=48,
winter_team=team_preventers,
summer_team=team_preventers,
)
session.add(hero_deadpond)
session.add(hero_rusty_man)
session.commit()
session.refresh(hero_deadpond)
session.refresh(hero_rusty_man)
print("Created hero:", hero_deadpond)
print("Created hero:", hero_rusty_man)
def select_heroes():
with Session(engine) as session:
winter_alias = aliased(Team)
# Heros with winter team as the Preventers using "aliases" and "onclause"
result = session.exec(
select(Hero)
.join(winter_alias, onclause=Hero.winter_team_id == winter_alias.id)
.where(winter_alias.name == "Preventers")
)
"""
SQL Looks like:
SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.winter_team_id, hero.summer_team_id
FROM hero JOIN team AS team_1 ON hero.winter_team_id = team_1.id
WHERE team_1.name = ?
"""
heros = result.all()
print("Heros with Preventers as their winter team:")
for hero in heros:
print(
f"Hero: {hero.name}, Winter Team: {hero.winter_team.name} Summer Team: {hero.summer_team.name}"
)
# Heros with Preventers as their winter team and Z-Force as their summer team using "has" function.
result = session.exec(
select(Hero)
.where(Hero.winter_team.has(Team.name == "Preventers"))
.where(Hero.summer_team.has(Team.name == "Z-Force"))
)
"""
SQL Looks like:
SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.winter_team_id, hero.summer_team_id
FROM hero
WHERE (
EXISTS (
SELECT 1 FROM team
WHERE team.id = hero.winter_team_id AND team.name = ?
)
) AND (
EXISTS (
SELECT 1 FROM team
WHERE team.id = hero.summer_team_id AND team.name = ?
)
)
"""
heros = result.all()
print(
"Heros with Preventers as their winter and Z-Force as their summer team:",
)
for hero in heros:
print(
f"Hero: {hero.name}, Winter Team: {hero.winter_team.name} Summer Team: {hero.summer_team.name}"
)
assert heros[0].name == "Deadpond"
def main():
create_db_and_tables()
create_heroes()
select_heroes()
if __name__ == "__main__":
main()