Directional Many-to-many self referencing ORM in SQLAlchemy using Association Pattern

Question

I tried to look for quite a while and didn't find a solution to my specific problem, so I apologize in advance.

Suppose I have the following model with tables Users and Follows:

     ________         src      ________ 
    |  User  |-1------------*-| Follow | 
    |--------|                |--------| 
    | id     |                | src_id |
    |        |                | dst_id |
    |        |                | string |
    |________|-1------------*-|________|
                      dst

Notice that that there are different semantics depending on the foreign keys.

I'm trying to achieve this through the "association pattern" (described here), but I can get it to work. It looks something like this:

class Follow(Base):
    __tablename__ = 'follows'
    #
    src_id = Column(BigInteger, ForeignKey('users.id'), primary_key=True)
    dst_id = Column(BigInteger, ForeignKey('users.id'), primary_key=True)

    src = relationship("User", back_populates="followers", foreign_keys=[src_id])
    dst = relationship("User", back_populates="followees", foreign_keys=[dst_id])

    kind = Column(String(16))

class User(Base):
    __tablename__ = 'users'

    name = Column(String(20))

    followers = relationship("UUEdge", primaryjoin="User.id==UUEdge.dst_id")
    followees = relationship("UUEdge", primaryjoin="User.id==UUEdge.src_id")

Is this possible? Am I doing something wrong?

Cheers

P.S.

Similar question that does not answer mine:

How can I achieve a self-referencing many-to-many relationship on the SQLAlchemy ORM back referencing to the same attribute?

What's that `UUEdge` that you've not included? You've also used `back_populates`, in `Follow`, but not the other side. It'd also help if you'd specify a clear problem definition. "I can('t) get it to work" is vague. — Ilja Everilä, Sep 12 '17 at 06:20
for a proper [mcve], make sure that the code demonstrates the problem you're having, and reproduces it, and also add the possible error messages you're getting. — Antti Haapala -- Слава Україні, Sep 12 '17 at 06:28

score 0 · Answer 1 · answered Sep 12 '17 at 07:22

This is how I implemented a follower relationship in my project using a join table.

followers = Table(
    'followers', metadata,
    Column('user_id', Integer,
        ForeignKey('users.user_id'), primary_key=True),
    Column('follower_id', Integer,
        ForeignKey('users.user_id'), primary_key=True),
)

class User(Base):

    __tablename__ = 'users'

    user_id = Column(Integer, primary_key=True)

    followers = relationship(
        'User', secondary=followers, cascade='all', lazy='dynamic',
        primaryjoin=followers.c.user_id == user_id,
        secondaryjoin=followers.c.follower_id == user_id)


# do some following!
user = session.query(User).get(..)
follower = User()
user.followers.append(follower)

Directional Many-to-many self referencing ORM in SQLAlchemy using Association Pattern

1 Answers1