上文讲了自引用数据模型的一对多场景，主要用于构建树形数据结构。自引用数据模型还有多对多的场景，比如文章引用，参考等等，这个时候需要引入一张中间表来完成。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
from typing import List, Optional

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


class PaperSimilarLink(SQLModel, table=True):
    paper_id: Optional[int] = Field(
        default=None, foreign_key="paper.id", primary_key=True
    )
    similar_id: Optional[int] = Field(
        default=None, foreign_key="paper.id", primary_key=True
    )


class Paper(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    similar: List["Paper"] = Relationship(
        link_model=PaperSimilarLink,
        sa_relationship_kwargs={
            "primaryjoin": "Paper.id == PaperSimilarLink.paper_id",
            "secondaryjoin": "Paper.id == PaperSimilarLink.similar_id",
        },
    )


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


def test() -> None:
    with Session(engine) as session:
        SQLModel.metadata.create_all(engine)

        p1 = Paper(id=1)
        p2 = Paper(id=2)
        p3 = Paper(id=3)
        session.add_all([p1, p2, p3])
        session.commit()

        session.refresh(p1)
        session.refresh(p2)
        session.refresh(p3)

        p1.similar = [p2, p3]
        session.add(p1)
        session.commit()

        p2.similar = [p1]
        session.add(p1)
        session.commit()

        statement = select(Paper)
        paper = session.exec(statement).unique().all()

        for i in paper:
            print(i.id, i.similar)


if __name__ == "__main__":
    test()


来源：https://github.com/fastapi/sqlmodel/issues/127