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?

Self-referential table case has a section in documentation: https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#self-referential-many-to-many — dmitry_romanov, Aug 28 '22 at 09:26

score 24 · Answer 1 · edited Aug 28 '22 at 07:02

24

You can also do this declaratively.

Here is a similar example based on the above code, I do use the backref.

VolumeRelationship = Table(
    'VolumeRelationship', Base.metadata,
    Column('ParentID', Integer, ForeignKey('Volumes.ID')),
    Column('VolumeID', Integer, ForeignKey('Volumes.ID'))
    )

class Volume(Base):
    """ Volume Object """
    __tablename__ = "Volumes"

    id = Column('ID', Integer, primary_key=True, nullable=False)
    type = Column('Type', String(25))
    name = Column('Name', String(25))
    poolid = Column('pool', Integer, ForeignKey('Pools.ID'))
    parents = relation(
                    'Volume',secondary=VolumeRelationship,
                    primaryjoin=VolumeRelationship.c.VolumeID==id,
                    secondaryjoin=VolumeRelationship.c.ParentID==id,
                    backref="children")

edited Aug 28 '22 at 07:02

dmitry_romanov

5,146
1
33
36

answered Apr 13 '11 at 16:01

Kurt Telep

721
1
5
9

3

For me, I had to add analogue of `foreign_keys = [VolumeRelationship.c.VolumeID, VolumeRelationship.c.ParentID])` to `Volume.parents`, otherwise I had `NoReferencedTableError`. – Mikhail Batcer Jun 08 '14 at 15:45
Self-referential table case has a section in documentation: https://docs.sqlalchemy.org/en/14/orm/join_conditions.html#self-referential-many-to-many – dmitry_romanov Aug 28 '22 at 09:26

Denis Otkidach · Accepted Answer · 2009-12-11T20:24:44.060

10

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

edited Dec 11 '09 at 20:24

answered Dec 11 '09 at 17:28

Denis Otkidach

32,032
8
79
100

Thanks, this worked perfectly. Did you mean that the follows table doesn't require an ID column and can use a composite PK? I don't see how that could work with the users table. – Travis Dec 11 '09 at 19:29
Yes, it was a mistake. I meant follows table. – Denis Otkidach Dec 11 '09 at 20:27
I ran into this and had to do it declaratively, here's the equivalent for future finders. – Kurt Telep Apr 13 '11 at 16:00
1

@DenisOtkidach can you elaborate simply what secondary join and primaryjoin does? – Zion Aug 08 '15 at 00:46
As @Zion says, would be helpful to elaborate on the primary and secondary joins. However here is the doc: http://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#specifying-alternate-join-conditions – melledijkstra Jun 05 '18 at 16:02

SQLAlchemy Many-to-Many Relationship on a Single Table

2 Answers2

Linked