How to implement a "Citation" table? (using SQLModel or SQLAlchemy)

Question

I'm struggling with implementing the concept of "scientific paper citation" in SQL.

I have a table of Papers. Each Paper can cite many other Papers and, vice-versa, it can be cited by many other more.

Here's the code I wrote

class Paper(SQLModel, table=True):
    id: Optional[int] = Field(default=None,  primary_key=True)
    bibliography: List["Citation"] = Relationship(back_populates="citing")
    cited_by: List["Citation"] = Relationship(back_populates="cited")

class Citation(SQLModel, table=True):
    citing_id: Optional[int] = Field(default=None, primary_key=True, foreign_key="paper.id")
    citing: "Paper" = Relationship(back_populates="bibliography")
    cited_id: Optional[int] = Field(default=None, primary_key=True, foreign_key="paper.id")
    cited: "Paper" = Relationship(back_populates="cited_by")

This is not working:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Paper.bibliography - 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.

The problem is the fact that I wrote foreign_key="paper.id" twice, but I don't know how to fix it.

---

To reproduce the error:

• I'm using Python 3.10.5;
• the only dependency is sqlmodel.

from typing import List
from typing import Optional
from sqlmodel import create_engine
from sqlmodel import Field
from sqlmodel import Relationship
from sqlmodel import Session
from sqlmodel import SQLModel

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

# class Paper(SQLModel, table=True): ...

# class Citation(SQLModel, table=True): ...

if __name__ == "__main__":
    SQLModel.metadata.create_all(engine)
    Paper()

I'm using SQLModel, but an answer in SQLAlchemy would be fine as well.

Answer 1

Handling multiple possible JOIN conditions in SQLAlchemy is documented here. The solution is to explicitly pass the foreign_keys argument to your RelationshipProperty constructor.

In this case, you will need to specify that for all four relationships in question.

Since SQLModel currently does not allow to directly pass all available relationship arguments to its constructor (though I am working on a PR for that), you need to utilize the sa_relationship_kwargs parameter.

Here is a working example:

from typing import Optional
from sqlmodel import Field, Relationship, SQLModel


class Paper(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    bibliography: list["Citation"] = Relationship(
        back_populates="citing",
        sa_relationship_kwargs={"foreign_keys": "Citation.citing_id"},
    )
    cited_by: list["Citation"] = Relationship(
        back_populates="cited",
        sa_relationship_kwargs={"foreign_keys": "Citation.cited_id"},
    )

class Citation(SQLModel, table=True):
    citing_id: Optional[int] = Field(
        default=None,
        primary_key=True,
        foreign_key="paper.id",
    )
    citing: Paper = Relationship(
        back_populates="bibliography",
        sa_relationship_kwargs={"foreign_keys": "Citation.citing_id"},
    )
    cited_id: Optional[int] = Field(
        default=None,
        primary_key=True,
        foreign_key="paper.id",
    )
    cited: Paper = Relationship(
        back_populates="cited_by",
        sa_relationship_kwargs={"foreign_keys": "Citation.cited_id"},
    )

As a side note, I think in this case it might be even nicer to use an association proxy to have an additional direct link from a paper to all papers it is cited by and citing (without the additional "hop" via the Citation object), but I believe this is currently not possible with SQLModel.