Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Multiple many-to-many self referencing relationships #89

Open
8 tasks done
AartGoossens opened this issue Sep 10, 2021 · 6 comments
Open
8 tasks done

Multiple many-to-many self referencing relationships #89

AartGoossens opened this issue Sep 10, 2021 · 6 comments
Labels
question Further information is requested

Comments

@AartGoossens
Copy link

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

from typing import List, Optional

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine


class UserLink(SQLModel, table=True):
    parent_id: Optional[int] = Field(
        default=None, foreign_key="user.id", primary_key=True
    )
    child_id: Optional[int] = Field(
        default=None, foreign_key="user.id", primary_key=True
    )


class User(SQLModel, table=True):
    id: int = Field(default=None, primary_key=True)
    parents: List["User"] = Relationship(back_populates="children", link_model=UserLink)
    children: List["User"] = Relationship(back_populates="parents", link_model=UserLink)


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
SQLModel.metadata.create_all(engine)


with Session(engine) as session:
    user_1 = User(id=1)
    session.add(user_1)

    user_2 = User(id=2, parents=[user_1])
    session.add(user_2)

    session.commit()

    session.refresh(user_1)
    session.refresh(user_2)

    assert len(user_1.children) == 1
    assert len(user_1.parents) == 0

    assert len(user_2.children) == 0
    assert len(user_2.parents) == 1

Description

I am trying to create a model with multiple many-to-many relationships referencing self. See example code.

My code raises this error:

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

My code is based on the many-to-many example in the docs.

As the error is coming from sqlalchemy it might also be an sqlalchemy issue but as it is raised by an example that is coming from the docs I think I might be using SQLmodel incorrectly.

I did read the sqlalchemy docs on many-to-many relationships but they did not help me.

This issue on stackoverflow seems related but I cannot figure out how to apply it to my situation.

Questions:

  • Am I correct in the assumption that I need to use many-to-many relationships with a link model?
  • ...or could the same be achieved by using a one-to-many with back_populates?
  • The sqlalchemy error is quite cryptic (at least to me). I seem to already have specified the foreign_key on the UserLink model columns but apparently I need to specify it differently or somewhere else as well. What else is needed to make this work?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.5

Additional Context

No response

@AartGoossens AartGoossens added the question Further information is requested label Sep 10, 2021
@maresb
Copy link

maresb commented Sep 12, 2021

I'm fairly new to this, but I seem to have managed to get it to work with

class User(SQLModel, table=True):
    id: int = Field(default=None, primary_key=True)
    parents: List["User"] = Relationship(
        back_populates="children",
        link_model=UserLink,
        sa_relationship_kwargs=dict(
            primaryjoin="User.id==UserLink.child_id",
            secondaryjoin="User.id==UserLink.parent_id",
        ),
    )
    children: List["User"] = Relationship(
        back_populates="parents",
        link_model=UserLink,
        sa_relationship_kwargs=dict(
            primaryjoin="User.id==UserLink.parent_id",
            secondaryjoin="User.id==UserLink.child_id",
        ),
    )

I'm not sure whether or not this is the "correct" way. I'm curious about what more expert people have to say.

My inspiration was Self-Referential Many-to-Many Relationship from the SQLAlchemy docs.

@AartGoossens
Copy link
Author

@maresb This works! You are my hero! 🦸 Awesome.

I think I tried something similar by passing sqlalchemy.orm.relationship() directly to sqlmodel.Relationship() as the sa_relationship kwarg but I must have missed something there. Using sa_relationship_kwargs also seems a bit nicer as you do not have to import from sqlalchemy.

And thanks for the link to the documentation, I did not find this page before. It really helps to understand what information/configuration SQLmodel/sqlalchemy need to make these self-referential relationships.

Thanks a lot!

@maresb
Copy link

maresb commented Sep 13, 2021

I'm glad I could help!

None of this stuff is documented on the SQLModel side, and I wouldn't be surprised if tiangolo wants to solve this in some more elegant way. Maybe it's better to leave this issue open until a maintainer has a look?

@AartGoossens
Copy link
Author

That's a good point. I'll reopen the issue.

@cashewpillar
Copy link

I tried the

from typing import List, Optional

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine

class UserLink(SQLModel, table=True):
    parent_id: Optional[int] = Field(
        default=None, foreign_key="user.id", primary_key=True
    )
    child_id: Optional[int] = Field(
        default=None, foreign_key="user.id", primary_key=True
    )

class User(SQLModel, table=True):
    id: int = Field(default=None, primary_key=True)
    parents: List["User"] = Relationship(
        back_populates="children",
        link_model=UserLink,
        sa_relationship_kwargs=dict(
            primaryjoin="User.id==UserLink.child_id",
            secondaryjoin="User.id==UserLink.parent_id",
        ),
    )
    children: List["User"] = Relationship(
        back_populates="parents",
        link_model=UserLink,
        sa_relationship_kwargs=dict(
            primaryjoin="User.id==UserLink.parent_id",
            secondaryjoin="User.id==UserLink.child_id",
        ),
    )

sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
SQLModel.metadata.create_all(engine)


with Session(engine) as session:
    user_1 = User(id=1)
    session.add(user_1)

    user_2 = User(id=2, parents=[user_1])
    session.add(user_2)

    session.commit()

    session.refresh(user_1)
    session.refresh(user_2)

    assert len(user_1.children) == 1
    assert len(user_1.parents) == 0

    assert len(user_2.children) == 0
    assert len(user_2.parents) == 1

But I get the error

AttributeError: 'User' object has no attribute 'children'

Where did I make a mistake?

@AartGoossens
Copy link
Author

@Iionsroar I think this is not related to this issue, but this one #322.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
question Further information is requested
Projects
None yet
Development

No branches or pull requests

3 participants