[FIXED] Users cannot follow groups in Flask (SQLAlchemy)

Issue

Error:

sqlalchemy.orm.exc.UnmappedColumnError
sqlalchemy.orm.exc.UnmappedColumnError: Can't execute sync rule for source column 'user.id'; mapper 'mapped class Group->group' does not map this column.  Try using an explicit `foreign_keys` collection which does not include destination column 'group_followers.group_followed_id' (or use a viewonly=True relation).

I'm trying to build a system that users can follow groups.

Here is the error parts of code:

group_followers = db.Table('group_followers',
    db.Column('group_follower_id', db.Integer, db.ForeignKey('group.id')),
    db.Column('group_followed_id', db.Integer, db.ForeignKey('group.id'))
)

class User(UserMixin, db.Model):
...
    group_followed = db.relationship(
            'Group', secondary = group_followers,
            primaryjoin = (group_followers.c.group_follower_id == id),
            secondaryjoin = (group_followers.c.group_followed_id == id),
            backref = db.backref('group_followers', lazy='dynamic'), lazy='dynamic')
...
    def group_follow(self, group):
           if not self.is_following(group):
                self.group_followed.append(group)

Solution

You need to clearly define the schema of your two tables.

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    groups = db.relationship('Group', backref='user', lazy='dynamic')
    
    def __repr__(self):
        return f'User {self.username}'
    
class Group(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    timestamp = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    # You can add any other field you want
    # ...
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))    

Above, I have shown how to create a relationship from the perspective of a user using db.relationship(). To make this relationship, you will need to add a ForeignKey() in the table you want to reference. user in db.ForeingKey(user.id) refers to the User table.

The association table you have created above (group_followers) is not part of any models so add it outside any of the classes User and Group:

group_followers = db.Table('group_followers',
    db.Column('group_follower_id', db.Integer, db.ForeignKey('group.id')),
    db.Column('group_followed_id', db.Integer, db.ForeignKey('group.id'))
)

Then, declare the many-to-many relationship in the User's table:

class User(UserMixin, db.Model):
...
    group_followed = db.relationship(
            'Group', secondary = group_followers,
            primaryjoin = (group_followers.c.group_follower_id == id),
            secondaryjoin = (group_followers.c.group_followed_id == id),
            backref = db.backref('group_followers', lazy='dynamic'), lazy='dynamic')

    def group_follow(self, group):
           if not self.is_following(group):
                self.group_followed.append(group)

Run your migrations to update and apply these changes:

(venv)$ flask db migrate -m 'group followers'
(venv)$ flask db upgrade

This should work for a user who wants to follow a group. You can define other functions to unfollow a group or check whether a user is already following a group.

Answered By - Gitau Harrison