0

I am trying to represent clubs and users in a web app using SQLAlchemy. Users must have a list of clubs, and clubs must have a list of users. I think I have done this correctly with a many-to-many relationship, since users can be in multiple clubs. However, I cannot make a club leader. The idea is that a leader should still be able to lead multiple clubs, but each club only has one leader.

I have tried combining different combinations of relationships, but cannot get a leader to be added at all.

user_club_assoc_table = db.Table('user_club_assoc_table',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('club_id', db.Integer, db.ForeignKey('club.id')))

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String(15), nullable=False)
    lastname = db.Column(db.String(15), nullable=False)
    email = db.Column(db.String(60), unique=True, nullable=False)
    password = db.Column(db.String(60), nullable=False)
    clubs = db.relationship('Club', secondary=user_club_assoc_table)


class Club(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    members = db.relationship('User', secondary=user_club_assoc_table)
    # This is what is needed
    # preferably be able to get all clubs being led by leader
    leader = db.relationship(...)

I expect to be able to create users and create clubs. A club should take a user that will be a leader as an argument when it is instantiated.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Paul Morenkov
  • 59
  • 4
  • Possible duplicate of [How to define two relationships to the same table in SQLAlchemy](https://stackoverflow.com/questions/7548033/how-to-define-two-relationships-to-the-same-table-in-sqlalchemy) – zwirbeltier Aug 04 '19 at 06:38
  • There doesn't seem to be anything in your SQL schema that'd determine which user is a leader of a club, so an ORM relationship is going to be somewhat challenging to define. – Ilja Everilä Aug 04 '19 at 07:41

1 Answers1

1

The two comments will get you where you need to go. Suggested code:

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    # ...
    clubs = db.relationship('Club', secondary=user_club_assoc_table,
                            backref=db.backref('members', lazy='dynamic'))

class Club(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)

    leader_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    leader = db.relationship('User', primaryjoin='club.leader_id == user.id')
Nick K9
  • 3,885
  • 1
  • 29
  • 62
  • When I try to create an account now, I get a long error that ends with ```sqlalchemy.exc.InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Triggering mapper: 'mapped class Club->club'. Original exception was: 'Table' object has no attribute 'advisor_id'``` where advisor is leader but renamed – Paul Morenkov Aug 10 '19 at 05:57
  • Have you upgraded your database to the new schema? – Nick K9 Aug 10 '19 at 08:57
  • It looks like I have. I have done a little testing and it seems like any time a query occurs there is an error. First, it happens under ```validate_email()``` for a registration form, where I do ```user = User.query.filter_by(email=email.data).first()```. If I return early in front of that, then the error moves to ```load_user()```, which is defined in models.py, using flask-login. There, the query is ```User.query.get(user_id)```. – Paul Morenkov Aug 10 '19 at 09:44