CODEWITHSUNDEEP
pythonflask-sqlalchemy
Joe cool
Joe cool

Reputation: 51

Flask-SQLAlchemy -- One model having two different relationships

I have three models: Role, User, and Post. Role -> User is one to many, and User -> Post is one to many. When I just had User and Post, everything worked. However, my website will need varying degrees of authorization. 

class Role(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    users = db.relationship('User', backref='role', lazy=True)


class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False)
    posts = db.relationship('Post', backref='author', lazy=True)


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

Here is the error message I get:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) 
no such column: user.role_id

Thanks!

Upvotes: 0

Views: 292

Answers (1)

Amber
Amber

Reputation: 526523

You don't need to explicitly define the role_id field - the relationship defined in Role (and the backref parameter you specified) has already created a backwards relationship on User objects for you. (The SQLAlchemy documentation around backrefs may be helpful to you.)

As such, if you have an instantiated User object u, you should be able to get details about the user's role via u.role (which will give you a Role instance) and the ID of the role as u.role.id.

Thus, your full set of model definitions only needs to look like this:

class Role(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    users = db.relationship('User', backref='role', lazy=True)


class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    posts = db.relationship('Post', backref='author', lazy=True)


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)

If you do this, then for a Post object p, you can get the id of the role of the author of the post as p.author.role.id (for example).

Upvotes: 1

Related Questions