I am working on a user structure where a user can have parents and children that consist of user objects. I have been trying to get the following to work in multiple different ways in SQLAlchemy and Flask.
Here is an example of how I want to structure this:
UserTable
id | name
---+-----
1 | Kim
2 | Tammy
3 | John
4 | Casey
5 | Kyle
UserRelationship
id | parent_user_id | child_user_id
---+----------------+---------------
1 | 1 | 2
2 | 1 | 3
3 | 4 | 2
Where Kim is the parent to Tammy and John. Casey is the parent of Tammy. Tammy is the child of Kim and Casey. John is the child of Kim. Kyle has no children nor parents.
My error reads:
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition
between parent/child tables on relationship User.parents - there are multiple
foreign key paths linking the tables via secondary table 'user_relationship'.
Specify the 'foreign_keys' argument, providing a list of those columns which
should be counted as containing a foreign key reference from the secondary
table to each of the parent and child tables.
My model.py file looks like:
user_relationship = db.Table(
'user_relationship',
db.Model.metadata,
db.Column('child_user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('parent_user_id', db.Integer, db.ForeignKey('user.id'))
)
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True, unique=True, nullable=False)
email = db.Column(db.String(120), unique=True)
pwdhash = db.Column(db.String(54))
parents = relationship('User',
secondary=user_relationship,
backref=db.backref('children'),
cascade="all,delete")
This might not be the best way to handle many to many hierarchical user relationships in Flask or SQLAlchemy. Any insight on how to build this out would be great.
Thank you
db.Table is mainly used when there is a Many to Many relation between two different entities. Here both parent and child are Users.
In your case, below code would be fine:
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True, unique=True, nullable=False)
email = db.Column(db.String(120), unique=True)
pwdhash = db.Column(db.String(54))
class Parent(db.Model):
__tablename__ = 'parents'
child_id = db.Column(db.Integer, db.Foreignkey('users.id'))
parent_id = db.Column(db.Integer, db.Foreignkey('users.id'))
child = db.relationship('User', foreign_keys=[child_id], backref = 'parents')
flask_sqlalchemy is written on top of SQLAlchemy, and there is a nice elaboration of this issue here (Handling Multiple Join Paths) in SQLAlchemy's website.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments