CODEWITHSUNDEEP
pythonsqlalchemyflaskflask-sqlalchemy
Tim Bueno
Tim Bueno

Reputation: 401

Self Referential Relationship in SQLAlchemy

I am trying to create a self referential relationship in SQLAlchemy.

I have a class 'Issue' that may have identical 'Issue' siblings that are exactly the same but have a different cover image attribute.

I would like each model to contain an attribute containing a list of its siblings but not itself if possible.

My issue model currently looks like this:

class Issue(db.Model):
    __tablename__ = 'issues'
    # IDs
    id = db.Column(db.Integer, primary_key=True)
    title_id = db.Column(db.Integer, db.ForeignKey('titles.id'))
    publisher_id = db.Column(db.Integer, db.ForeignKey('publishers.id'))
    parent_id = db.Column(db.Integer, db.ForeignKey('issues.id'))
    # Attributes
    product_id = db.Column(db.String(100))
    issue_number = db.Column(db.Float)
    .......
    # Relationships
    is_parent = db.Column(db.Boolean(), default=False)
    alternates = db.relationship('Issue',
                backref=db.backref('parent', remote_side=[id])
            )

Each Issue has an 'is_parent' attribute that I set when it is determined that the issue is the 'base' issue. However, I still want all siblings to be able to access one another.

Currently, I am giving the 'parent' issue a relationship with all of its 'children'/'siblings'.

The problem occurs when I am interacting with an instance of a 'sibling'. I would like to directly access all of its relatives but I must go through the parent first.

Any help would be greatly appreciated!

EDIT: Basically, I would like to create a self-referential many-to-many relationship. Am I wrong in believing this is the correct direction to pursue?

Upvotes: 2

Views: 6123

Answers (2)

rockets4all
rockets4all

Reputation: 876

I know this is old, but the op could also just use a self referential relational scheme to find the children easily. Here is how that would work in your group.

class Issue(db.Model):
    __tablename__ = 'issues'
    # IDs
    id = db.Column(db.Integer, primary_key=True)
    title_id = db.Column(db.Integer, db.ForeignKey('titles.id'))
    publisher_id = db.Column(db.Integer, db.ForeignKey('publishers.id'))
    parent_id = db.Column(db.Integer, db.ForeignKey('issues.id'))
    # Attributes
    product_id = db.Column(db.String(100))
    issue_number = db.Column(db.Float)
    .......
    # Relationships
    #is_parent = db.Column(db.Boolean(), default=False)
    alternates = db.relationship('Issue',
                backref=db.backref('parent', remote_side=[id])
            )
    #This is what you need to add to make the database link it self
    parent_id=db.Column(db.Integer, db.ForeignKey('issues.id'))
    children=db.relationship('Issue', backref=db.backref('parent', remote_side=[id]))

Calling children would send you all the children of the parent. Calling parent would give you the parent of the current group. If it returns None then you are looking at a root Issue.

Upvotes: 2

Miguel Grinberg
Miguel Grinberg

Reputation: 67479

What you are doing is not a self-referential many-to-many, it is a self-referential one-to-many (one parent has many children).

First of all, you do not need to have the is_parent boolean. You can easily determine if an issue is a parent by looking at the parent_id field, which would point to itself only for parents. You could add the following helper method to your Issue class:

def is_parent(self):
    return self.parent_id == self.id

To obtain the siblings of an issue you can write a custom query that gets all the issues that have the same parent_id except the one the query is issued on:

def get_siblings(self):
    return Issue.query.filter(Issue.parent_id == self.parent_id, Issue.id != self.id).all()

Upvotes: 3

Related Questions