CODEWITHSUNDEEP
pythonsqlalchemy
MarcusWilliams
MarcusWilliams

Reputation: 562

Flask + SQLalchemy one to many relationship within the same table not working

I am trying to create a table in SQLAlchemy which has a one to many relationship within the same table. What I currently have is a dog class and I want a dog to be able to link to multiple children and I also want the children to be able to link back to their parents. I am currently using an SQLite database if that makes a difference

There have been some similar posts on this topic, for example here and here, and here. I have also tried using the documentation on Adjacency List Relations, but I have not been able to find any solution that is working for me.

Here is my current code:

class Dog(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    birthday = db.Column(db.DateTime, index=True)
    sex = db.Column(db.String(64))
    comments = db.Column(db.String(1000))

    fathered = db.relationship("Dog", backref = db.backref("father", remote_side=[id]))
    mothered = db.relationship("Dog", backref = db.backref("mother", remote_side=[id]))
    father_id = db.Column(db.Integer, db.ForeignKey('dog.id'))
    mother_id = db.Column(db.Integer, db.ForeignKey('dog.id'))

When I try to run my flask app I am met with this error

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Dog.fathered - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

Based on that error I also tried supplying a list of foreign keys to the relationship, like this

fathered = db.relationship("Dog", backref = db.backref("father", remote_side=[id]), foreign_keys = [id])
mothered = db.relationship("Dog", backref = db.backref("mother", remote_side=[id]), foreign_keys = [id])

But that also led to an error:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Dog.fathered - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Any help would be greatly appreciated, apologies if I am missing something simple, or if there is a much simpler way to do this that I don't know, thank you.

Upvotes: 0

Views: 1157

Answers (1)

MarcusWilliams
MarcusWilliams

Reputation: 562

The answer was simpler than I thought and was something I thought I tried. You need to set the foreign keys of fathered and mothered to father_id and mother_id respectivley rather than just the id. So this is the solution code that seems to be working:

father_id = db.Column(db.Integer, db.ForeignKey('dog.id'))
mother_id = db.Column(db.Integer, db.ForeignKey('dog.id'))
fathered = db.relationship("Dog", foreign_keys = [father_id], backref = db.backref("father", remote_side=[id]))
mothered = db.relationship("Dog", foreign_keys = [mother_id], backref = db.backref("mother", remote_side=[id]))

Also, obviously make sure to move fathered and mothered below the father_id and mother_id, otherwise you won't be able to use the id's before they have been declared.

Thanks a lot to the comments on the post for helping with this one!

Upvotes: 1

Related Questions