I am trying to model a complex relationship using SQLModel with Postgresql database. In that, I am querying the User and Subscriber which, between them has a many-to-one relationship as well as an established F'Key. Similarly, User has a m:1 relationship with UserRole, and Subscriber also has m:1 relationships with SubscriberType and 1:m with Address.
Address in turn has other m:1 and 1:1 relationships with other classes and corresponding tables; but for the sake of brevity, let's skip them. Accordingly I skinned out my model to show here the core issue.
I think what it is not liking is that Subscriber has a FK to Address and Address has a FK to Subscriber, which may in turn create multiple FK relation paths between the two. If my interpretation of the error is right in relation to the model, then I do not know the best way to break / represent it. What is the best practice in such cases?
Biz situation is that a Subscriber, a Supplier, a Partner all have an address and may even multiple addresses. So, each has a 1:m to Address. An Address, in turn can belong to any of those entities and thus will have a FK to point to the right one. My representation may not be correct but do not know how else to do this.
Admittedly I have started to learn both Python and ORM (SQLModel); so my apologies in advance for naive mistakes and gratitude to help me understand my mistakes and guide me to the right solution.
I tried three options with the same results - thank God at least for that. It confirmed my understanding on modeling the relationships and /or creating the joined query is totally off.
What I saw in other posts and suggestions to similar issues calling for adding explicit foreign_keys[] in the relationship of the child class while omitting the relationship from the parent. I couldn't do this as it kept complaining about unexpected argument.
Many also suggested to use sa_relationship_kwargs=dict(lazy="selectin") in the child relationship; but candidly, I did not quite understand how this works or why this could solve the issue, to be able to properly use it.
Here's my code. All three options are annotated inline in the code below.
from decouple import config
from sqlalchemy import text
from sqlmodel import SQLModel, Field, Relationship, select, Session, join, and_
from typing import Optional, List
from connection import engine
dbschema = config('dbschema') # application actors details
scschema = config('scschema') # biz objects
class SubscriberType(SQLModel, table=True):
__tablename__ = 'subscriber_types_test'
__table_args__ = {'schema': dbschema}
type: str = Field(default=None, primary_key=True)
subscribers: List['Subscriber'] = Relationship(back_populates="subscribertype")
class UserRole(SQLModel, table=True):
__tablename__ = 'user_roles_test'
__table_args__ = {'schema': dbschema}
role: str = Field(default=None, primary_key=True)
users: List['User'] = Relationship(back_populates="userrole")
class Subscriber(SQLModel, table=True):
__tablename__ = 'subscribers_test'
__table_args__ = {'schema': dbschema}
id: Optional[int] = Field(default=None, primary_key=True)
name: str = Field(nullable=False, index=True)
address_id: Optional[int] = Field(foreign_key=scschema + '.addresses_test.id', index=True)
addresses: List['Address'] = Relationship(back_populates='subscriber')
subscriber_type: str = Field(nullable=False, default='regular',
foreign_key=dbschema + '.subscriber_types_test.type'
)
subscribertype: SubscriberType = Relationship(back_populates='subscribers')
users: List['User'] = Relationship(back_populates='subscriber')
class User(SQLModel, table=True):
__tablename__ = 'users_test'
__table_args__ = {'schema': dbschema}
id: Optional[int] = Field(default=None, primary_key=True)
subscriber_id: int = Field(nullable=False, foreign_key=dbschema + '.subscribers_test.id', index=True)
subscriber: Subscriber = Relationship(back_populates='users')
email: str = Field(nullable=False, unique=True, index=True)
username: str = Field(nullable=False)
role: str = Field(nullable=False, default='user', foreign_key=dbschema + '.user_roles_test.role')
userrole: UserRole = Relationship(back_populates='users')
class Address(SQLModel, table=True):
__tablename__ = 'addresses_test'
__table_args__ = {'schema': scschema}
id: Optional[int] = Field(default=None, primary_key=True)
lat: Optional[float]
lon: Optional[float]
subscriber_id: Optional[int] = Field(foreign_key=dbschema + '.subscribers_test.id', index=True)
subscriber: Optional['Subscriber'] = Relationship(back_populates='addresses')
# Below was my first option without selective columns and criteria
# stmt = select(User, Subscriber).select_from(join(User, Subscriber))
# Tried this option with more detailed query and filters and this runs perfectly in PGAdmin portal
stmt = text('Select U.username as "username", \
U.email as "email", \
U.role as "user_role", \
S.name as "subscribername" \
From test.users_test U, test.subscribers_test S \
Where U.email = "[email protected]" and U.subscriber_id = S.id'
)
with (Session(engine) as session):
SQLModel.metadata.create_all(session.bind)
substype = SubscriberType(type="regular")
role = UserRole(role="admin")
addr = Address()
subs = Subscriber(name="xyz corp")
user = User(email="[email protected]", username="abc")
subs.subscribertype = substype
addr.subscriber = subs
user.userrole = role
session.add(substype)
session.add(role)
session.add(addr)
session.add(subs)
session.add(user)
session.commit()
# Below statement used with the first two options
# data = session.exec(stmt)
# This was my third option
data = session.query(user.email, user.username, user.role, subs.name
).where(and_(user.email == '[email protected]', user.subscriber_id == subs.id)
).first()
print(data)
I expect the result returned should be:
[("abc", "[email protected]", "regular", "xyz corp")]
But I am getting the following error (full Traceback):
Traceback (most recent call last):
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/orm/relationships.py", line 2754, in _determine_joins
self.primaryjoin = join_condition(
^^^^^^^^^^^^^^^
File "<string>", line 2, in join_condition
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/sql/selectable.py", line 1217, in _join_condition
cls._joincond_trim_constraints(
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/sql/selectable.py", line 1338, in _joincond_trim_constraints
raise exc.AmbiguousForeignKeysError(
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'subscribers_test' and 'addresses_test'; 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 "/Users/tapas/PycharmProjects/ResiChainLab/multirelationtest/main.py", line 89, in <module>
substype = SubscriberType(type="regular")
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "<string>", line 4, in __init__
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/orm/state.py", line 476, in _initialize_instance
manager.dispatch.init(self, args, kwargs)
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/event/attr.py", line 334, in __call__
fn(*args, **kw)
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/orm/mapper.py", line 3716, in _event_on_init
instrumenting_mapper._check_configure()
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/orm/mapper.py", line 1941, in _check_configure
_configure_registries({self.registry}, cascade=True)
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/orm/mapper.py", line 3527, in _configure_registries
_do_configure_registries(registries, cascade)
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/orm/mapper.py", line 3566, in _do_configure_registries
mapper._post_configure_properties()
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/orm/mapper.py", line 1958, in _post_configure_properties
prop.init()
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/orm/interfaces.py", line 231, in init
self.do_init()
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/orm/relationships.py", line 2152, in do_init
self._setup_join_conditions()
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/orm/relationships.py", line 2248, in _setup_join_conditions
self._join_condition = jc = JoinCondition(
^^^^^^^^^^^^^^
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/orm/relationships.py", line 2643, in __init__
self._determine_joins()
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/orm/relationships.py", line 2806, in _determine_joins
util.raise_(
File "/Users/tapas/Anaconda/anaconda3/envs/multirelationtest/lib/python3.11/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Subscriber.addresses - 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.
What am I missing to take note of? Where are the "multiple foreign key paths" coming from?