SQLAlchemy two relationships to same table

Question

What I'm trying to do is probably pretty simple, but I've spent at least a few hours on solving this and I might just be overcomplicating things here, but I'd love a bit of assistance, bonus points for elaboration.

NOTE: I have read up on this, and tried incorporating what I think the solution for me is based off the accepted answer, but for some reason I still haven't been able to solve this

class Orders(db.Model)
    id = db.Column(db.Integer, primary_key=True)
    dest_address = db.relationship('Address', backref='orders')
    from_address = db.relationship('Address', backref='orders')

class Address(db.Model)
    id = db.Column(db.Integer, primary_key=True)
    order_id = db.Column(db.Integer, db.ForeignKey('orders.id'))

So basically, I want 2 seperate one-to-one relationships within a specific model that points the same model for each relationship, and I'm not entirely sure how to go about doing that correctly

I've tried adding a is_dest_address = db.Column(db.Boolean, default=True) field to my address Class and specifying a primaryjoin condition within the relationships, as well as defining the foreign_keys params in the relationship (The foreign_keys params are Address.id)

But I'm not entirely sure as to how to go about doing this correctly.

Also, it might be worth mentioning that my User model has a relationship to the Orders model, as well as a relationship to the Address model

I've also tried this which feels the closest out of all the various things I've tried:

class Address(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    is_dest_address = db.Column(db.Boolean, default=True)

class Orders(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    dest_address_id = db.Column(db.Integer, db.ForeignKey('address.id'))
    from_address_id = db.Column(db.Integer, db.ForeignKey('address.id'))
    dest_address = db.relationship('Address', backref='orders', uselist=False, foreign_keys=[dest_address_id])
    from_address = db.relationship('Address', backref='orders', uselist=False, foreign_keys=[from_address_id])

And I get this error: sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Orders.from_address - 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.

Ideally I could just use the latter code and specify a primaryjoin or something similar to determine which Address is stored as which field/relationship, based on if the is_dest_address is True/False

NOTE: I'm doing exactly what the documentation says to do as far as I know https://docs.sqlalchemy.org/en/14/orm/join_conditions.html

Answer 1

Solved with

class Orders(db.Model):
  dest_address = db.relationship('Address', backref='orders_dest_address')
  from_address = db.relationship('Address', backref='orders_from_address')

class Address(db.Model):
  order_id = db.Column(db.Integer, db.ForeignKey('orders.id'))