SQLAlchemy Many-to-Many Relationship on a Single Table

Question

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)
    )

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?

Answer 1

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),
        ),
    },
)

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.

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).