How to set one, many, and one relationship at the same time in Flask-SQLAlchemy?

Question

I am trying to create a one to one and one to many relationships at the same time in Flask-SQLAlchemy. I want to achieve this:

"The group has many members and one administrator."

Here is what I did:

class Group(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(140), index=True, unique=True) description = db.Column(db.Text) created_at = db.Column(db.DateTime, server_default=db.func.now()) members = db.relationship('User', backref='group') admin = db.relationship('User', backref='admin_group', uselist=False) def __repr__(self): return '<Group %r>' % (self.name) class User(db.Model): id = db.Column(db.Integer, primary_key=True) group_id = db.Column(db.Integer, db.ForeignKey('group.id')) admin_group_id = db.Column(db.Integer, db.ForeignKey('group.id')) created_at = db.Column(db.DateTime, server_default=db.func.now()) 

However, I received an error message:

sqlalchemy.exc.AmbiguousForeignKeysError: Failed to define a join condition between parent / child tables in relation to Group.members - there are several foreign key paths connecting the tables. Specify 'foreign_keys' containing a list of those columns that should be considered as containing the foreign key reference for the parent table.

Does anyone know how to do this correctly?

Answer 1

The problem you get is that you defined two links between your classes: the user has group_id (which is the foreign key), and the group has an admin (which is also defined by the Foreign key). If you remove the foreign key from the admin field, the connection is no longer ambiguous and the job will work. This is my solution to your problem (creating a one-on-one link):

 from app import db,app class Group(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(140), index=True, unique=True) description = db.Column(db.Text) created_at = db.Column(db.DateTime, server_default=db.func.now()) admin_id = db.Column(db.Integer) #, db.ForeignKey('user.id')) members = db.relationship('User', backref='group') def admin(self): return User.query.filter_by(id=self.admin_id).first() class User(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80), unique=True) group_id = db.Column(db.Integer, db.ForeignKey('group.id')) created_at = db.Column(db.DateTime, server_default=db.func.now()) 

The only drawback to this is that the group object does not have an elementary member member admin , which you can simply use - you must call the group.admin() function to retrieve the administrator. However, a group can have many members, but only one of them can be an administrator. Obviously, there is no database level check to ensure that the administrator is actually a member of the group, but you can add this check to the setter function - perhaps something like:

 # setter method def admin(self, user): if user.group_id == self.id: self.admin_id = user.id # getter method def admin(self): return User.query.filter_by(id=self.admin_id).first() 

Answer 2

The solution is to specify the foreign_keys argument for all relationship s:

 class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) group_id = Column(Integer, ForeignKey('groups.id')) admin_group_id = Column(Integer, ForeignKey('groups.id')) class Group(Base): __tablename__ = 'groups' id = Column(Integer, primary_key=True) members = relationship('User', backref='group', foreign_keys=[User.group_id]) admin = relationship('User', backref='admin_group', uselist=False, foreign_keys=[User.admin_group_id]) 

Perhaps consider the administrator’s attitude in the other direction to implement “a group has many members and one administrator”:

 class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) group_id = Column(Integer, ForeignKey('groups.id')) group = relationship('Group', foreign_keys=[group_id], back_populates='members') class Group(Base): __tablename__ = 'groups' id = Column(Integer, primary_key=True) members = relationship('User', foreign_keys=[User.group_id], back_populates='group') admin_user_id = Column(Integer, ForeignKey('users.id')) admin = relationship('User', foreign_keys=[admin_user_id], post_update=True) 

Check out post_update in the documentation . This is necessary when the two models are interdependent, referring to each other.

Answer 3

Ok, I found a workaround for this problem, finally. Many-to-many relationships can coexist with a one-to-many relationship between the same two tables at the same time.

Here is the code:

 groups_admins = db.Table('groups_admins', db.Column('user_id', db.Integer, db.ForeignKey('user.id')), db.Column('group_id', db.Integer, db.ForeignKey('group.id')) ) class Group(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(140), index=True, unique=True) description = db.Column(db.Text) created_at = db.Column(db.DateTime, server_default=db.func.now()) members = db.relationship('User', backref='group') admins = db.relationship('User', secondary=groups_admins, backref=db.backref('mod_groups', lazy='dynamic'), lazy='dynamic') def __repr__(self): return '<Group %r>' % (self.name) class User(db.Model): id = db.Column(db.Integer, primary_key=True) group_id = db.Column(db.Integer, db.ForeignKey('group.id')) created_at = db.Column(db.DateTime, server_default=db.func.now()) 

I still want someone to tell me how to establish a one-to-many and one-to-one relationship at the same time, so I leave my answer here and will not accept it forever.