Relationships in SQLalchemy

Question

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?

May I suggest a change to the schema. Why do you need mat_id and pat_id separately? You can use two columns: ID and sex_flag (boolean value). This should solve your problem since you now have 1 column to reference. Is this appropriate change in your situation? — Vladimir S., Sep 19 '15 at 08:06
This is a pre-existing schema that is used by other software so changing it is not likely to be feasible, at least in the short term. Could you expand on your suggestion a bit though? It might be worth exploring it at some point in the future. — Blackmyre, Sep 19 '15 at 10:02
Ah, I think I see what you mean: an additional joining table with two (parentid/childid) rows per child? Yes, I guess that should work and might well have been a better design if I were starting from here. I'd prefer a solution given the existing schema, but thanks for the suggestion - I would upvote it but I'm new here so I don't seem to have permission to do that yet. — Blackmyre, Sep 19 '15 at 10:32

Ilja Everilä · Answer 1 · 2017-12-29T12:35:14.913

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.

Thanks, I'll take a look at that. This project was little more than an excuse to take a first look at Python and SQLAlchemy and was abandoned a couple of years ago. As you know from another question though I've now started to look at it again for another project, so the technique may still prove useful. — Blackmyre, Dec 29 '17 at 13:55

Relationships in SQLalchemy

1 Answers1