Many to many relationship between a table and itself

[joaopfg]

Many to many relationship between a table and itself

[daniil-berg]

What you can already do is set up relationships back-and-forth between the Node and Edge tables, such that

1. every Node has a list of all outgoing edges and a list of all incoming edges and
2. every Edge has references to its "to"- and "from"-nodes.

Here is an example:

from typing import Optional

from sqlmodel import Field, Relationship, SQLModel


class Node(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    edges_out: list["Edge"] = Relationship(
        back_populates="from_node",
        sa_relationship_kwargs={
            "foreign_keys": "Edge.from_node_id",
            "lazy": "selectin",
        },
    )
    edges_in: list["Edge"] = Relationship(
        back_populates="to_node",
        sa_relationship_kwargs={
            "foreign_keys": "Edge.to_node_id",
            "lazy": "selectin",
        },
    )


class Edge(SQLModel, table=True):
    from_node_id: Optional[int] = Field(
        default=None,
        foreign_key="node.id",
        primary_key=True,
    )
    from_node: Optional[Node] = Relationship(
        back_populates="edges_out",
        sa_relationship_kwargs={"foreign_keys": "Edge.from_node_id"},
    )
    to_node_id: Optional[int] = Field(
        default=None,
        foreign_key="node.id",
        primary_key=True,
    )
    to_node: Optional[Node] = Relationship(
        back_populates="edges_in",
        sa_relationship_kwargs={"foreign_keys": "Edge.to_node_id"},
    )

    def __repr__(self) -> str:
        return f"Edge({self.from_node_id} -> {self.to_node_id})"

The foreign_keys keyword argument is important. (See SQLAlchemy docs here.)

This allows you to quite a few things already. To get adjacency lists however you will still need the additional "hop" via the related edge objects.

Here is a demo:

def main() -> None:
    from sqlmodel import Session, create_engine, select

    engine = create_engine("sqlite:///", echo=True)
    SQLModel.metadata.create_all(engine)
    session = Session(engine)
    n1, n2, n3 = Node(), Node(), Node()
    session.add_all([n1, n2, n3])
    e1 = Edge(from_node=n1, to_node=n2)
    e2 = Edge(from_node=n2, to_node=n3)
    e3 = Edge(from_node=n1, to_node=n3)
    session.add_all([e1, e2, e3])
    session.commit()
    nodes = session.execute(select(Node)).scalars().all()
    for node in nodes:
        print(f"{node.id=}")
        print(f"  {node.edges_out=}")
        print(f"  {node.edges_in=}")
        print(f"  to_nodes={[edge.to_node.id for edge in node.edges_out]}")
        print(f"  from_nodes={[edge.from_node.id for edge in node.edges_in]}")


if __name__ == "__main__":
    main()

Output:

node.id=1
  node.edges_out=[Edge(1 -> 2), Edge(1 -> 3)]
  node.edges_in=[]
  to_nodes=[2, 3]
  from_nodes=[]
node.id=2
  node.edges_out=[Edge(2 -> 3)]
  node.edges_in=[Edge(1 -> 2)]
  to_nodes=[3]
  from_nodes=[1]
node.id=3
  node.edges_out=[]
  node.edges_in=[Edge(2 -> 3), Edge(1 -> 3)]
  to_nodes=[]
  from_nodes=[2, 1]

It would be really nice, if we could define a to_nodes and a from_nodes attribute on Node directly to avoid going via the Edge instances. But for this we need the association proxy from SQLAlchemy to work and I believe it is currently not supported in SQLModel. (At least I could not get it to work right now.)

Support for a custom association proxy might be a worthwhile feature in the future, but should maybe not be high priority IMHO.

[ClanEver]

Looks the same as this #89

[christianholland]

Thanks @daniil-berg, your answer already helped to improve my understanding about advanced usage of relationships!

In your answer above you have solved the case (as you stated) that every "Node" has a list of all outgoing edges and a list of all incoming edges.

While this should also be true for my use case I need also that edges can have multiple incoming and outgoing nodes. Just think about a receipe where (simplified) Dough and Butter make a Cake. Dough, Butter and Cake are Node instances and the baking process is an instance of the Edge model. Of course at the same time you could use the node Butter also for a different baking edge.

Ideally, the Edge model should also have additional fields (e.g. to stay with the baking analogy, the baking temperature).

I tried to update your solution by setting in the Edge model the relationships from_node and to_note as a List but this did not work unfortunately.

[jenkinchang]

Use "remote_side" in "sa_relationship_kwargs"

class Node(SQLModel, table=True):
    id: Optional[int] = Field(primary_key=True)
    name: str

    father_id: Optional[int] = Field(foreign_key="node.id")
    father: Optional["Node"] = Relationship(
        back_populates="childs",
        sa_relationship_kwargs={"remote_side": "Node.id"}
    )
    childs: List["Node"] = Relationship(back_populates="father")

[vedangjadhav88]

• I split the Edge class into two classes for better clarity.

• Each Node has two separate relationships (out_edges and in_edges) to the Edge class. One for outgoing edges and the other for incoming edges.

• I defined corresponding relationships (from_node and to_node) in the Edge class.

This way, you can still retrieve the incoming and outgoing edges for each node using the relationships defined in the Node class. The downside is that you have to query both relationships separately when retrieving the adjacency lists.

 `from typing import List, Optional

 from sqlmodel import Field, Relationship, SQLModel


class Edge(SQLModel, table=True):
from_node_id: Optional[int] = Field(default=None, foreign_key="node.id")
to_node_id: Optional[int] = Field(default=None, foreign_key="node.id")

class Node(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)

# Outgoing edges
out_edges: List[Edge] = Relationship(back_populates="from_node", link_model=Edge, field="from_node_id")

# Incoming edges
in_edges: List[Edge] = Relationship(back_populates="to_node", link_model=Edge, field="to_node_id")

class` Edge(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
from_node_id: Optional[int] = Field(default=None, foreign_key="node.id")
to_node_id: Optional[int] = Field(default=None, foreign_key="node.id")

# Define relationships to Node
from_node: Node = Relationship(back_populates="out_edges", link_model=Edge, field="from_node_id")
to_node: Node = Relationship(back_populates="in_edges", link_model=Edge, field="to_node_id")

`

[LordPhwa]

This seems to work for me for a self-referential many-to-many relationship with relationship attributes.

class Node(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    edge_to: list["Edge"] = Relationship(
        back_populates="from_node",
        sa_relationship_kwargs={
            "primaryjoin": "Node.id==Edge.from_node_id",
        },
    )

    edge_from: list["Edge"] = Relationship(
        back_populates="to_node",
        sa_relationship_kwargs={
            "primaryjoin": "Node.id==Edge.to_node_id",
        },
    )


class Edge(SQLModel, table=True):
    from_node_id: int = Field(default=None, foreign_key="node.id", primary_key=True)
    from_node: "Node" = Relationship(
        back_populates="edge_to",
        sa_relationship_kwargs={"primaryjoin": "Edge.from_node_id==Node.id"},
    )

    to_node_id: int = Field(default=None, foreign_key="node.id", primary_key=True)
    to_node: "Node" = Relationship(
        back_populates="edge_from",
        sa_relationship_kwargs={"primaryjoin": "Edge.to_node_id==Node.id"},
    )

    link_type: str | None