How to resolve AmbiguousForeignKeysError because of multiple foreign keys

Question

I have two model Users & PatientDetails

class Users(Base):
    __tablename__ = "users"
    __table_args__ = (
        Index("email", "email", unique=True),
        Index("mobile_number", "mobile_number", unique=True),
    )

    id = Column(String(36), primary_key=True)
    first_name = Column(String(20), nullable=False)
    last_name = Column(String(20), nullable=False)
    email = Column(String(50), nullable=False)
    mobile_number = Column(String(13), nullable=False)
    password = Column(String(100), nullable=False)
    is_active = Column(Boolean, nullable=False, default=False)
    picture = Column(String(100), nullable=True)

    role_id = Column(ForeignKey("roles.id"), nullable=False)
    role = relationship("Roles", back_populates="user")

    provider_details = relationship("ProviderDetails")
    provider_practitioners = relationship("ProviderPractitioner")
    patient_details = relationship("PatientDetails")
    patient_diagnosis = relationship("PatientDiagnosis")
    invites = relationship("Invites", back_populates="invited_by_user")

class PatientDetails(Base):
    __tablename__ = "patient_details"
    __table_args__ = (
        Index("patient_basic_details_id", "patient_basic_details_id", unique=True),
    )

    id = Column(String(36), primary_key=True)
    sex = Column(String(10), nullable=False)
    dob = Column(Date, nullable=False)
    is_married = Column(Boolean, nullable=False)
    spouse_name = Column(String(20), nullable=True)
    emergency_contact_number = Column(String(13), nullable=True)
    allergies = Column(ForeignKey("allergies.id"), nullable=False)
    address = Column(String(200), nullable=False)
    long = Column(String(100), nullable=False)
    lat = Column(String(100), nullable=False)

    patient_basic_details_id = Column(ForeignKey("users.id"), nullable=False)
    patient_basic_details = relationship("User")
    provider_basic_details_id = Column(ForeignKey("users.id"), nullable=False)
    provider_basic_details = relationship("User")

By looking at the model you may have understood that PatientDetails has two foreign keys from Users. Also in Users I have defined relationship with PatientDetails. This is because I want to travel from user's object to patients details object as a user can be a patients and the patient related information is in PatientDetails and I also was to travel from patients details object to users object as a patient will have a provider and a provider is nothing but another user.

But I get this exception -

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/relationships.py", line 2749, in _determine_joins
    self.primaryjoin = join_condition(
  File "<string>", line 2, in join_condition
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/selectable.py", line 1217, in _join_condition
    cls._joincond_trim_constraints(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/selectable.py", line 1338, in _joincond_trim_constraints
    raise exc.AmbiguousForeignKeysError(
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'users' and 'patient_details'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/code/app/seed_data.py", line 10, in create_roles
    admin_role = Roles(id=get_uuid(), name="Admin")
  File "<string>", line 4, in __init__
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/state.py", line 476, in _initialize_instance
    manager.dispatch.init(self, args, kwargs)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/event/attr.py", line 334, in __call__
    fn(*args, **kw)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 3716, in _event_on_init
    instrumenting_mapper._check_configure()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 1941, in _check_configure
    _configure_registries({self.registry}, cascade=True)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 3527, in _configure_registries
    _do_configure_registries(registries, cascade)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 3566, in _do_configure_registries
    mapper._post_configure_properties()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 1958, in _post_configure_properties
    prop.init()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/interfaces.py", line 231, in init
    self.do_init()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/relationships.py", line 2147, in do_init
    self._setup_join_conditions()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/relationships.py", line 2243, in _setup_join_conditions
    self._join_condition = jc = JoinCondition(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/relationships.py", line 2638, in __init__
    self._determine_joins()
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/relationships.py", line 2801, in _determine_joins
    util.raise_(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 210, in raise_
    raise exception
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Users.patient_details - 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.

Answer 1

Relationship should understand what exactly FK are you trying to use

    patient_basic_details_id = Column(ForeignKey("users.id"), nullable=False)
    patient_basic_details = relationship("User", foreign_keys=patient_basic_details_id)
    provider_basic_details_id = Column(ForeignKey("users.id"), nullable=False)
    provider_basic_details = relationship("User", foreign_keys=provider_basic_details_id)