单个表上的SQLAlchemy多对多关系 [英] SQLAlchemy Many-to-Many Relationship on a Single Table

问题描述

我在应用程序中建立了一个SQLAlchemy模型，该模型应模仿Twitter上关注者"的功能.用户与彼此之间具有多对多关系(关注者和关注者).这些表的结构如下(sa是sqlalchemy模块):

I have a SQLAlchemy model set up in my application that should mimic the functionality of "followers" on Twitter, ie. users have have a many-to-many relationship with eachother (both followers and following). The tables are structured as follows (sa is the sqlalchemy module):

t_users = sa.Table("users", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("email", sa.types.String(320), unique=True, nullable=False),
    ...etc...
    )

t_follows = sa.Table("follows", meta.metadata,
    sa.Column("id", sa.types.Integer, primary_key=True),
    sa.Column("follower_id", sa.types.Integer, sa.ForeignKey('users.id'), nullable=False),
    sa.Column("followee_id", sa.types.Integer, sa.ForeignKey('users.id'), nullable=False)
    )

但是，我尝试使用orm.mapper创建这种关系时遇到了一些障碍，因为辅助表在两个方向上都引用回相同的主表.我该如何将这种关系映射到ORM?

I've run into a bit of a roadblock however, trying to use orm.mapper to create this relationship, since the secondary table refers back to the same primary table in both directions. How would I go about mapping this relationship to the ORM?

推荐答案

在这种情况下，您必须明确编写primaryjoin和secondaryjoin条件:

You have to write primaryjoin and secondaryjoin conditions explicitly in this case:

mapper(
    User, t_users,
    properties={
        'followers': relation(
            User,
            secondary=t_follows,
            primaryjoin=(t_follows.c.followee_id==t_users.c.id),
            secondaryjoin=(t_follows.c.follower_id==t_users.c.id),
        ),
        'followees': relation(
            User,
            secondary=t_follows,
            primaryjoin=(t_follows.c.follower_id==t_users.c.id),
            secondaryjoin=(t_follows.c.followee_id==t_users.c.id),
        ),
    },
)

我已经写了这个样本，以帮助您更好地理解primaryjoin和secondaryjoin参数的含义.当然，您可以使用backref将其分类.

I've wrote this sample verbose to help you understand better what primaryjoin and secondaryjoin parameters mean. Sure, you can make it sorter with backref.

顺便说一句，您不需要下表中的id列，而应使用复合主键.实际上，无论如何，您都应该定义follower_id和followee_id对的唯一约束(作为主键或其他唯一键).

BTW, you don't need id column in follows table, use composite primary key instead. In fact, you should define unique constraint of follower_id and followee_id pair anyway (either as primary or additional unique key).

这篇关于单个表上的SQLAlchemy多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！