CODEWITHSUNDEEP
pythonormsqlalchemy
Travis
Travis

Reputation: 4078

SQLAlchemy Many-to-Many Relationship on a Single Table

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?

Upvotes: 16

Views: 10710

Answers (2)

Kurt Telep
Kurt Telep

Reputation: 729

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

Upvotes: 24

Denis Otkidach
Denis Otkidach

Reputation: 33180

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

Upvotes: 11

Related Questions