CODEWITHSUNDEEP
pythonsqlalchemyrelational
Blackmyre
Blackmyre

Reputation: 47

Relationships in SQLalchemy

SQL Alchemy handles adjacency structures just fine, e.g. a self-referential table node where foreign key node.parent_id relates to primary key node.id.

I have a less conventional model where a node has two parents (a genealogy database). In this, node.mat_id and node.pat_id relate to the maternal and paternal parent nodes respectively. SQL Alchemy is OK with this too:

mother = db.relationship("Node", 
    foreign_keys = "Node.mat_id", remote_side="Node.id")
father = db.relationship("Node", 
    foreign_keys = "Node.pat_id", remote_side="Node.id")

(using Declarative).

So getting both parents of a node is straight forward. My problem is with getting the children of a node with this setup. I can't find a way to set up a relationship the equivalent of:

offspring = db.relationship("Node", 
    foreign_keys = "Node.mat_id | Node.pat_id")

The best I have managed is to declare mat_offspring and pat_offspring relationships separately and create a member function offspring() that returns the appropriate one. It works but seems inelegant. Is there a better and more conventional way?

Upvotes: 2

Views: 437

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 53017

Given the original schema limitations, you were on the right track. Instead of foreign_keys you'd pass the join condition as the primaryjoin:

class Node(Base):
    __tablename__ = "node"

    id = Column(Integer, primary_key=True)
    mat_id = Column(ForeignKey(id))
    pat_id = Column(ForeignKey(id))

    mother = relationship("Node", foreign_keys=[mat_id], remote_side=id)
    father = relationship("Node", foreign_keys=[pat_id], remote_side=id)
    offspring = relationship("Node",
        primaryjoin=or_(id == mat_id, id == pat_id),
        viewonly=True)

Note that due to the more complex join condition the relationship is defined as view only. In other words you cannot simply add offspring to a Node through it, because you could not say if the parent node were the father or the mother of the newly added offspring. Your original separate maternal and paternal relationships are superior in that sense.

Upvotes: 1

Related Questions