Specifying one-to-many relationships explicitly without foreign keys

[achie27]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

# Variant #1

from typing import List, Optional
from sqlalchemy.orm import RelationshipProperty
from sqlmodel import SQLModel, Field, Relationship


class User(SQLModel, table=True):
    id: str = Field(primary_key=True)

    orders: Optional[List["Order"]] = Relationship(back_populates="user",
                                         sa_relationship=RelationshipProperty("Order",
                                                                              primaryjoin="User.id == Order.user_id"
                                                                              ))


class Order(SQLModel, table=True):
    txn_id: str = Field(primary_key=True)
    user_id: str = Field()

    user: User = Relationship(back_populates="orders",
                              sa_relationship=RelationshipProperty("User",
                                                                   primaryjoin="Order.user_id == User.id",
                                                                   foreign_keys=[user_id])
                              )


user = User(id="theuserid")
order1 = Order(txn_id="theorderid", user_id=user.id)


# =================================================================

# Variant #2

from typing import List, Optional
from sqlalchemy.orm import RelationshipProperty
from sqlmodel import SQLModel, Field, Relationship


class User(SQLModel, table=True):
    id: str = Field(primary_key=True)

    # orders: Optional[List["Order"]] = Relationship(back_populates="user",
    #                                      sa_relationship=RelationshipProperty("Order",
    #                                                                           primaryjoin="User.id == Order.user_id"
    #                                                                           ))


class Order(SQLModel, table=True):
    txn_id: str = Field(primary_key=True)
    user_id: str = Field()

    user: User = Relationship(back_populates="orders",
                              sa_relationship=RelationshipProperty("User",
                                                                   primaryjoin="Order.user_id == User.id",
                                                                   foreign_keys=[user_id])
                              )


user = User(id="theuserid")
order1 = Order(txn_id="theorderid", user_id=user.id)

Description

I'm using PlanetScale which doesn't support foreign keys and thus wanted to make use of relationships without them.
From reading up the docs and other issues, SQLAlchemy's primaryjoin looked to be the solution for this.

However, when running Variant #1, I get this exception -

Traceback (most recent call last):
  File "PROJECT_PATH/api/models/relationship_test.py", line 26, in <module>
    user = User(id="theuserid")
  File "<string>", line 4, in __init__
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/state.py", line 474, in _initialize_instance
    manager.dispatch.init(self, args, kwargs)
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/event/attr.py", line 343, in __call__
    fn(*args, **kw)
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 3610, in _event_on_init
    instrumenting_mapper._check_configure()
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 1901, in _check_configure
    _configure_registries({self.registry}, cascade=True)
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 3425, in _configure_registries
    _do_configure_registries(registries, cascade)
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 3464, in _do_configure_registries
    mapper._post_configure_properties()
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 1918, in _post_configure_properties
    prop.init()
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/interfaces.py", line 231, in init
    self.do_init()
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/relationships.py", line 2152, in do_init
    self._setup_join_conditions()
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/relationships.py", line 2248, in _setup_join_conditions
    self._join_condition = jc = JoinCondition(
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/relationships.py", line 2650, in __init__
    self._check_foreign_cols(self.primaryjoin, True)
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/relationships.py", line 3268, in _check_foreign_cols
    raise sa_exc.ArgumentError(err)
sqlalchemy.exc.ArgumentError: Could not locate any relevant foreign key columns for primary join condition '"user".id = "order".user_id' on relationship User.orders.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation.

Post this, I try ditching User.orders to at least get the Order side working, as shown in Variant #2, and get this exception -

Traceback (most recent call last):
  File "PROJECT_PATH/api/models/relationship_test.py", line 26, in <module>
    user = User(id="theuserid")
  File "<string>", line 4, in __init__
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/state.py", line 474, in _initialize_instance
    manager.dispatch.init(self, args, kwargs)
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/event/attr.py", line 343, in __call__
    fn(*args, **kw)
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 3610, in _event_on_init
    instrumenting_mapper._check_configure()
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 1901, in _check_configure
    _configure_registries({self.registry}, cascade=True)
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 3425, in _configure_registries
    _do_configure_registries(registries, cascade)
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 3464, in _do_configure_registries
    mapper._post_configure_properties()
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 1918, in _post_configure_properties
    prop.init()
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/interfaces.py", line 231, in init
    self.do_init()
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/relationships.py", line 2150, in do_init
    self._process_dependent_arguments()
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/relationships.py", line 2231, in _process_dependent_arguments
    self._user_defined_foreign_keys = util.column_set(
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/orm/relationships.py", line 2232, in <genexpr>
    coercions.expect(
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 188, in expect
    resolved = impl._literal_coercion(
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 371, in _literal_coercion
    self._raise_for_expected(element, argname)
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/sql/coercions.py", line 283, in _raise_for_expected
    util.raise_(exc.ArgumentError(msg, code=code), replace_context=err)
  File "PROJECT_PATH/env/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
sqlalchemy.exc.ArgumentError: Column expression expected for argument 'foreign_keys'; got FieldInfo(default=PydanticUndefined, extra={}).

How do I get relationship attributes working for such types of one-to-many relationships without using (db-level) foreign keys?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.10.4

Additional Context

The SQLAlchemy version is 1.4.35

[achie27]

I got it to work with

from typing import List, Optional
from sqlalchemy.orm import RelationshipProperty
from sqlmodel import SQLModel, Field, Relationship


class User(SQLModel, table=True):
    id: str = Field(primary_key=True)

    orders: Optional[List["Order"]] = Relationship(back_populates="user",
                                                   sa_relationship=RelationshipProperty("Order",
                                                                                        primaryjoin="foreign(User.id) == Order.user_id",
                                                                                        uselist=True))


class Order(SQLModel, table=True):
    txn_id: str = Field(primary_key=True)
    user_id: str = Field()

    user: User = Relationship(back_populates="orders",
                              sa_relationship=RelationshipProperty("User",
                                                                   primaryjoin="foreign(Order.user_id) == User.id",
                                                                   uselist=False))


user = User(id="theuserid")
order1 = Order(txn_id="theorderid", user_id=user.id)

[Jijun-TANG]

I got it to work with

from typing import List, Optional
from sqlalchemy.orm import RelationshipProperty
from sqlmodel import SQLModel, Field, Relationship


class User(SQLModel, table=True):
    id: str = Field(primary_key=True)

    orders: Optional[List["Order"]] = Relationship(back_populates="user",
                                                   sa_relationship=RelationshipProperty("Order",
                                                                                        primaryjoin="foreign(User.id) == Order.user_id",
                                                                                        uselist=True))


class Order(SQLModel, table=True):
    txn_id: str = Field(primary_key=True)
    user_id: str = Field()

    user: User = Relationship(back_populates="orders",
                              sa_relationship=RelationshipProperty("User",
                                                                   primaryjoin="foreign(Order.user_id) == User.id",
                                                                   uselist=False))


user = User(id="theuserid")
order1 = Order(txn_id="theorderid", user_id=user.id)

Thanks bro, it saves my day!
I don't know why it just doesn't work according to the documentation of many-to-many model: https://sqlmodel.tiangolo.com/tutorial/many-to-many/create-models-with-link/
And we need to add this "RelationshipProperty" from sqlalchemy. I chose sqlmodel just to avoid those complexities. And fine we need to patch this "simplicity" from time to time

[jflad17]

Thank you for this, I spent so long trying to get this to work in SQLModel and this fixed it!