Relationships¶

Introduction¶

Database tables are often related to one another. A blog post may have many comments, or an order could be related to the user who placed it. Arvent makes managing and working with these relationships easy, and supports the common relationship types: one-to-one, one-to-many, many-to-many, polymorphic, through, and recursive (tree) relationships.

In Arvent, a relationship is declared as a zero-argument method that returns a relationship builder. The framework detects these methods, wires them for querying, and makes them eager-loadable.

Defining Relationships¶

One to Many¶

A one-to-many relationship is used to define relationships where a single model is the parent to one or more child models. For example, a user may have many posts:

from arvel.database import Model, Timestamps, foreign_id, id_, string


class User(Model, Timestamps):
    __tablename__ = "users"
    id: int = id_()
    name: str = string(120)

    def posts(self):
        return self.has_many(Post)

One to One¶

A one-to-one relationship links exactly one related row, such as a user's profile:

class User(Model, Timestamps):
    __tablename__ = "users"
    id: int = id_()

    def profile(self):
        return self.has_one(Profile)

The Inverse: Belongs To¶

Define the inverse with belongs_to. A post belongs to its author:

class Post(Model, Timestamps):
    __tablename__ = "posts"
    id: int = id_()
    user_id: int = foreign_id("users.id")
    title: str = string(200)

    def author(self):
        return self.belongs_to(User)

Custom Keys¶

Arvent assumes the foreign key follows the {parent}_id convention and the local key is the parent's primary key. Override either when your schema differs:

def posts(self):
    return self.has_many(Post, foreign_key="author_id", local_key="id")


def author(self):
    return self.belongs_to(User, foreign_key="author_id", owner_key="id")

Querying Relationships¶

Because a relationship method returns a query builder, you can add constraints before running it. Call the method, chain query methods, then await a terminal:

recent = await (
    user.posts()
    .where(is_published=True)
    .order_by("-created_at")
    .get()
)

Relationship Write Helpers¶

Relationship builders add write helpers that set the foreign key for you.

has_many and has_one provide save, create, save_many, and create_many. Note that create takes a dict of attributes:

post = await user.posts().create({"title": "Hello"})
await user.posts().save_many([post_a, post_b])

belongs_to provides associate, dissociate, and with_default:

await post.author().associate(other_user)   # sets post.user_id
await post.author().dissociate()            # clears it

with_default(...) returns a placeholder model instead of None when no parent exists — handy for avoiding null checks in templates and resources.

Many to Many¶

Many-to-many relationships are slightly more involved — they use a pivot (join) table. Declare a BelongsToMany descriptor as a class attribute, pointing at the related model and the pivot table:

from typing import ClassVar
from sqlalchemy import Column, ForeignKey, Integer, Table
from arvel.database import Model, Timestamps, id_, string
from arvel.database.orm.belongs_to_many import BelongsToMany


# The pivot is a SQLAlchemy Table on the shared Model.metadata.
post_tag_table = Table(
    "post_tag",
    Model.metadata,
    Column("post_id", Integer, ForeignKey("posts.id"), primary_key=True),
    Column("tag_id", Integer, ForeignKey("tags.id"), primary_key=True),
)


class Tag(Model):
    __tablename__ = "tags"
    id: int = id_()
    name: str = string(50)


class Post(Model, Timestamps):
    __tablename__ = "posts"
    id: int = id_()

    tags: ClassVar[BelongsToMany[Tag]] = BelongsToMany(
        Tag,
        table=post_tag_table,
        foreign_key="post_id",
        related_foreign_key="tag_id",
    )

Read the related rows by awaiting the accessor's all(), or iterate it directly:

tags = await post.tags.all()

async for tag in post.tags:
    print(tag.name)

Managing the Pivot¶

The accessor exposes pivot management methods:

await post.tags.attach(tag_id)
await post.tags.detach(tag_id)
await post.tags.sync([1, 2, 3])               # exact set; detaches the rest
await post.tags.sync_without_detaching([4])   # add without removing
await post.tags.toggle(5)                     # attach if absent, detach if present
await post.tags.create(name="python")         # create related + attach

Note

create(...) takes the related model's attributes as keyword arguments. A lone positional dict binds to the pivot parameter, not the attributes.

Pivot Columns¶

When the pivot table carries extra columns, pass them when attaching and filter by them when querying:

await post.tags.attach(tag_id, added_by="system")

featured = await post.tags.where_pivot("added_by", "editor")
ordered = await post.tags.order_by_pivot("created_at")

Pivot data is passed as keyword arguments to attach. where_pivot and order_by_pivot are async and return the list of related models directly — there's no .all() to chain.

Warning

Pivot filters like where_pivot live on the BelongsToMany accessor only. Calling where_pivot on a plain query builder raises RuntimeError.

Polymorphic Relationships¶

A polymorphic relationship lets a model belong to more than one other model type on a single association. The classic example: comments that can attach to posts and videos.

Class	Role
`MorphOne(Related, name=...)`	One-to-one polymorphic
`MorphMany(Related, name=...)`	One-to-many polymorphic
`MorphTo(name=...)`	The inverse — resolves the parent
`MorphToMany(Related, table=, name=, related_key=)`	Many-to-many polymorphic
`MorphedByMany(...)`	The inverse of `MorphToMany`

from arvel.database.orm.morph import MorphMany, MorphTo


class Post(Model):
    __tablename__ = "posts"
    id: int = id_()
    comments: ClassVar[MorphMany["Comment"]] = MorphMany(Comment, name="commentable")


class Comment(Model):
    __tablename__ = "comments"
    id: int = id_()
    commentable: ClassVar[MorphTo] = MorphTo(name="commentable")

The relationship stores two columns — {name}_type and {name}_id. Access them by awaiting:

comments = await post.comments.all()
parent = await comment.commentable    # the Post or Video it belongs to

The Morph Map¶

By default the type column stores the model's class name. Register a morph map to store stable aliases instead, so renaming a class doesn't orphan existing rows:

from arvel.database import morph_map

morph_map({"post": Post, "video": Video})

Note

A partial morph map is fine: unmapped types fall back to the short class name. Call require_morph_map() to turn on strict mode, where an unmapped type raises MorphMapError instead of falling back.

Through Relationships¶

A "through" relationship reaches a distant relation via an intermediate model — for example, a country has many posts through users. Declare these as class methods:

class Country(Model):
    __tablename__ = "countries"
    id: int = id_()

    @classmethod
    def posts(cls):
        return cls.has_many_through(Post, User)

posts = await Country.has_many_through(Post, User).where(...).get()

has_one_through is the single-result variant.

Recursive Relationships¶

For self-referential trees (categories, comments, org charts), use the recursive relationships, which walk an adjacency list via a recursive CTE:

class Category(Model):
    __tablename__ = "categories"
    id: int = id_()
    parent_id: int | None = foreign_id("categories.id", nullable=True)

    def descendants(self):
        return self.has_many_recursive(parent_key="parent_id")

    def ancestors(self):
        return self.belongs_to_recursive(parent_key="parent_id")

tree = await category.descendants().with_max_depth(5).all()
nodes = await category.descendants().as_tree()   # nested TreeNode structure

Eager-load a whole tree with with_tree(...) — see below.

Eager Loading¶

Accessing a relationship per row causes the N+1 query problem — one query for the parents, then one more for each parent's relation. Eager loading fetches everything up front. Use with_ (note the trailing underscore — with is a Python keyword):

users = await User.with_("posts").get()

for user in users:
    await user.posts.all()   # already loaded; no extra query

Nested relations use dot notation:

await User.with_("posts.comments").get()

Note

Arvel's eager-load method is with_, not with. There is no with alias — Python reserves the keyword.

Constrained Eager Loading¶

To constrain the rows that are eager-loaded, pass a mapping of relation name to a closure that modifies the relation's query:

users = await User.with_({
    "posts": lambda q: q.where(Post.published == True),
}).get()

Lazy Eager Loading¶

When you already have the parent models, load relations onto them after the fact:

await user.load("posts", "profile")
await user.load_missing("posts")     # only loads if not already loaded

# on a whole collection
await users.load("posts")
await users.load_missing("posts")

To count related rows without loading them, use with_count. Other aggregates work the same way:

users = await User.with_count("posts").get()
# each user now carries posts_count

await User.with_sum("orders", "total").get()
await User.with_exists("posts").get()

You can alias the result with an "as" suffix, and load aggregates onto existing models with load_count, load_sum, and load_exists:

await User.with_count("comments as total").get()
await user.load_count("posts")

Querying Relationship Existence¶

To filter parents by whether they have related rows — without loading the relations — use has, where_has, and friends:

# users that have at least one post
await User.has("posts").get()

# users with at least 3 published posts
await User.where_has(
    "posts",
    lambda q: q.where(Post.published == True),
    operator=">=",
    count=3,
).get()

# users with no posts
await User.doesnt_have("posts").get()

# nested existence
await User.where_has("posts.comments").get()

where_relation is sugar for a where_has with a single column constraint:

await User.where_relation("posts", "status", "published").get()

For polymorphic existence, use where_has_morph and has_morph. See the query builder for the full filtering surface.

Relationships¶

Introduction¶

Defining Relationships¶

One to Many¶

One to One¶

The Inverse: Belongs To¶

Custom Keys¶

Querying Relationships¶

Relationship Write Helpers¶

Many to Many¶

Managing the Pivot¶

Pivot Columns¶

Polymorphic Relationships¶

The Morph Map¶

Through Relationships¶

Recursive Relationships¶

Eager Loading¶

Constrained Eager Loading¶

Lazy Eager Loading¶

Counting Related Models¶

Querying Relationship Existence¶