One to Many Relationship on same table in SQLAlchemy

Question

I am modeling Repositories on GitHub. Each Repo can have a set of forks, of which the Repo will be the parent of.

My partial model file looks like this:

class Repo(Base):
    __tablename__ = "repos"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text, unique=True)
    parent_id = db.Column(Integer,db.ForeignKey('repos.id'))
    parent = db.relationship("Repo")

I am having trouble wrapping my head around how to properly make this relationship.

score 4 · Accepted Answer · edited May 23 '17 at 12:33

You can use remote_side directive

class Repo(Base):
    __tablename__ = "repos"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text, unique=True)
    parent_id = db.Column(Integer,db.ForeignKey('repos.id'))
    forks = relationship("Repo",
                backref=backref('parent', remote_side=[id])
            )

See Adjacency List Relationships documentation and this answer

Busturdust · Answer 2 · 2016-01-13T20:23:42.027

1

Maybe something like this would work?

class Repo(Base):
    __tablename__ = "repos"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text, unique=True)


class Fork(Repo):
    __tablename__ = "forks"
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(Integer,db.ForeignKey('repos.id'))
    parent = db.relationship("Repo")

Inherit the Repo Class, so that the Fork shares all the same attributes as a Repo. Additionally, we add a relationship in the Fork which desginates it is not a root repository, and is associated to some previously forked repo

edited Jan 13 '16 at 20:23

answered Jan 13 '16 at 20:06

Busturdust

2,447
24
41

I haven't tried subclassing Models with SQLAlchemy, so this is a good idea. I tried your example, but go this error: `SAWarning: Could not assemble any primary keys for locally mapped table 'forks' - no rows will be persisted in this Table. self._configure_pks()` – George L Jan 13 '16 at 20:13
1

Its more than likely my example is not proper, as I did it on the fly to illustrate the concept, but check out http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/inheritance.html for subclassing ... I edited with an explicit ID column set to PK for fork – Busturdust Jan 13 '16 at 20:22
1

you can also research `polymorphic_identity` as in this SO question .... http://stackoverflow.com/questions/1337095/sqlalchemy-inheritance – Busturdust Jan 13 '16 at 20:25

One to Many Relationship on same table in SQLAlchemy

2 Answers2

Linked