How do 'primaryjoin' and 'secondaryjoin' work for many-to-many relationship in SQLAlchemy?

Question

Having some difficulty understanding some Flask-SQLAlchemy stuff from the Flask Mega Tutorial. Here's the code:

followers = db.Table('followers',     db.Column('follower_id', db.Integer, db.ForeignKey('user.id')),     db.Column('followed_id', db.Integer, db.ForeignKey('user.id')) )  class User(db.Model):     id = db.Column(db.Integer, primary_key = True)     nickname = db.Column(db.String(64), unique = True)     email = db.Column(db.String(120), index = True, unique = True)     role = db.Column(db.SmallInteger, default = ROLE_USER)     posts = db.relationship('Post', backref = 'author', lazy = 'dynamic')     about_me = db.Column(db.String(140))     last_seen = db.Column(db.DateTime)     followed = db.relationship('User',          secondary = followers,          primaryjoin = (followers.c.follower_id == id),          secondaryjoin = (followers.c.followed_id == id),          backref = db.backref('followers', lazy = 'dynamic'),          lazy = 'dynamic')      def follow(self, user):         if not self.is_following(user):             self.followed.append(user)             return self      def unfollow(self, user):         if self.is_following(user):             self.followed.remove(user)             return self      def is_following(self, user):         return self.followed.filter(followers.c.followed_id == user.id).count() > 0 

So I understand that because this is a self-referential relationship, we need some way for the association table to figure out which User in the table is the follower and which User in the table is the one being followed. Primaryjoin and secondaryjoin accomplish this, but how?

Three things I don't understand about primaryjoin and secondaryjoin are as follows:

1. What's the purpose of primaryjoin and secondaryjoin checking equality? Or, in other words, how exactly does primaryjoin and secondaryjoin add user.ids to the association table?
2. Since both primaryjoin and secondaryjoin take a user.id requirement, which user.id goes where?
3. In my follow/unfollow methods, how does SQLAlchemy know that self is the follower and that the user passed in is the one being followed?

These questions have been holding me back from moving on to the next chapter, so any answers are very appreciated.

Answer 1

1. In a many to many relationship, the primaryjoin expression describes the join between the left table and the junction table, and the secondaryjoin describes the join between the junction table and the right table. In other words, the primaryjoin expression is saying, "find all rows in the followers table where follower_id is X", the secondaryjoin expression is saying "find all rows in the followers table where followed_id is X", and get those two together to find all users that follow user X, and all users that are followed by user X.

2. It depends on the direction you're querying from. When you ask for user.followers, it will find them by using the primaryjoin to query the followers table for all rows where followed_id == user.id, and retrieve the user other with other.id == follower_id. When you ask for user.followed, it uses the secondaryjoin to query the followers table for all rows where follower_id == user.id, and retrieve the user other with other.id == followed_id.

3. Because you're adding it to self.followed collection, telling SQLAlchemy that's someone self is following. If you were adding it to the self.followers collection, you'd be doing the inverse, telling SQLAlchemy that 'user' is a follower of self.

Reference: SQLAlchemy documentation for specifying alternative join conditions (primaryjoin and secondaryjoin).