SQLAlchemy relationship() via intermediary table

I am struggling to define methods in SQLAlchemy to retrieve related records via an intermediary table.

Consider the following schema:

• Users can create multiple posts, each post belongs to 1 user
• Each post can have multiple comments on it, with each comment belonging to 1 post

What I want is to be able to, for a given user instance, retrieve all of the comments from all of their posts.

I have set this up as follows:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

class Base(DeclarativeBase):
    id: Mapped[int] = mapped_column(primary_key=True)

# define model classes
class User(Base):
    __tablename__ = "users"

    name: Mapped[str] = mapped_column()

    posts: Mapped[list["Post"]] = relationship(back_populates="user")

    def __repr__(self) -> str:
        return f"(<{__class__.__name__}> name: {self.name})"


class Post(Base):
    __tablename__ = "posts"

    title: Mapped[str] = mapped_column()

    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    user: Mapped["User"] = relationship(back_populates="posts")

    comments: Mapped[list["Comment"]] = relationship(back_populates="post")

    def __repr__(self) -> str:
        return f"(<{__class__.__name__}> title: {self.title})"


class Comment(Base):
    __tablename__ = "comments"

    body: Mapped[str] = mapped_column()

    post_id: Mapped[int] = mapped_column(ForeignKey("posts.id"))
    post: Mapped["Post"] = relationship(back_populates="comments")

    def __repr__(self) -> str:
        return f"(<{__class__.__name__}> body: {self.body})"

If I create a few instances of these models, you can see how things are related:

# create instances
user = User(name="greta")

post_1 = Post(title="First post", user=user)
post_2 = Post(title="Second post", user=user)

comment_1 = Comment(body="yeah wotever", post=post_1)
comment_2 = Comment(body="lol good one", post=post_1)
comment_3 = Comment(body="lmfao", post=post_2)

# show all posts, and their comments
print(user)
for post in user.posts:
    print(f"   └── {post}")
    for comment in post.comments:
        print(f"          └── {comment}")

(<User> name: greta)
   └── (<Post> title: First post)
          └── (<Comment> body: yeah wotever)
          └── (<Comment> body: lol good one)
   └── (<Post> title: Second post)
          └── (<Comment> body: lmfao)

I am unsure of how to use relationship() to define a method all_comments() in the User class, which would return a list of all of the comments across all of a user instance's posts.

Can anyone point me in the right direction?

Using your secondary table posts, you can use secondaryjoin and primaryjoin in relationship to get what you want.

This way you needn't create a method all_comments, you can just do user.comments and get the comments, <strike>also the other way round, comment.user gives you the user.</strike> (see edit)

You probably want to tweak the join conditions, from my rudimentary testing this seems to get where you want to be.

I have created two users, which different set of posts and comments so you can see the difference.

from sqlalchemy import ForeignKey, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session


class Base(DeclarativeBase):
    id: Mapped[int] = mapped_column(primary_key=True)


class User(Base):
    __tablename__ = "users"

    name: Mapped[str] = mapped_column()

    posts: Mapped[list["Post"]] = relationship(back_populates="user")

    comments: Mapped[list["Comment"]] = relationship(
        back_populates="user",
        secondary="posts",
        primaryjoin="User.id == Post.user_id",
        secondaryjoin="Comment.post_id == Post.id",
        viewonly=True,
    )

    def __repr__(self) -> str:
        return f"(<{__class__.__name__}> name: {self.name})"


class Post(Base):
    __tablename__ = "posts"

    title: Mapped[str] = mapped_column()

    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    user: Mapped["User"] = relationship(back_populates="posts")

    comments: Mapped[list["Comment"]] = relationship(back_populates="post")

    def __repr__(self) -> str:
        return f"(<{__class__.__name__}> title: {self.title})"


class Comment(Base):
    __tablename__ = "comments"

    body: Mapped[str] = mapped_column()

    post_id: Mapped[int] = mapped_column(ForeignKey("posts.id"))
    post: Mapped["Post"] = relationship(back_populates="comments")

    user: Mapped["User"] = relationship(
        back_populates="comments",
        secondary="posts",
        primaryjoin="User.id == Post.user_id",
        secondaryjoin="Comment.post_id == Post.id",
        viewonly=True,
        uselist=False,
    )

    def __repr__(self) -> str:
        return f"(<{__class__.__name__}> body: {self.body})"


engine = create_engine("sqlite:///temp.db")


Base.metadata.create_all(engine)

with Session(engine) as session, session.begin():
    user = User(name="greta")

    post_1 = Post(title="First post", user=user)
    post_2 = Post(title="Second post", user=user)

    comment_1 = Comment(body="yeah wotever", post=post_1)
    comment_2 = Comment(body="lol good one", post=post_1)
    comment_3 = Comment(body="lmfao", post=post_2)

    session.add_all((user, post_1, post_2, comment_1, comment_2, comment_3))

    user = User(name="not greta")

    post_1 = Post(title="Third post", user=user)
    post_2 = Post(title="Fourth post", user=user)

    comment_1 = Comment(body="wotever", post=post_1)
    comment_2 = Comment(body="good one", post=post_1)

    session.add_all((user, post_1, post_2, comment_1, comment_2))


with Session(engine) as session:
    statement = select(User)

    for user in session.scalars(statement):
        print(user, user.comments)

Output

(<User> name: greta) [(<Comment> body: yeah wotever), (<Comment> body: lol good one), (<Comment> body: lmfao)]
(<User> name: not greta) [(<Comment> body: wotever), (<Comment> body: good one)]

Edit: The reverse relation "get user from a comment" seems to be bugged in this implementation, one comment has more than one user, I am not sure where I went wrong, but if all you want is the relation "get all comments for a user" then this works.