python - 覆盖 sqlalchemy 中的关系行为

假设我以声明方式有 Parent 、 Child 和 Pet 三个表，这样

Parent 与 Child 和 Pet

都存在多对多关系

Child 与 Pet

是一对多的关系

它们的代码是(使用 Flask-SQLAlchemy，尽管我相信该解决方案存在于 SQLAlchemy 领域而不是 Flask 中)。

class Parent(db.Model):
    __tablename__ = 'parents'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))

    # many to many relationship between parent and children
    # my case allows for a children to have many parents. Don't ask.
    children = db.relationship('Child',
                           secondary=parents_children_relationship,
                           backref=db.backref('parents', lazy='dynamic'),
                           lazy='dynamic')

    # many to many relationship between parents and pets
    pets = db.relationship('Pet',
                             secondary=users_pets_relationship,
                             backref=db.backref('parents', lazy='dynamic'), #
                             lazy='dynamic')

# many to many relationship between parents and children
parents_children_relationship = db.Table('parents_children_relationship',
    db.Column('parent_id', db.Integer, db.ForeignKey('parents.id')),
    db.Column('child_id', db.Integer, db.ForeignKey('children.id')),
    UniqueConstraint('parent_id', 'child_id'))

# many to many relationship between User and Pet
users_pets_relationship = db.Table('users_pets_relationship',
    db.Column('parent_id', db.Integer, db.ForeignKey('parents.id')),
    db.Column('pet_id', db.Integer, db.ForeignKey('pets.id')),
    UniqueConstraint('parent_id', 'pet_id'))

class Child(db.Model):
    __tablename__ = 'children'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    # parents = <backref relationship with User model>

    # one to many relationship with pets
    pets = db.relationship('Pet', backref='child', lazy='dynamic')


class Pet(db.Model):
    __tablename__ = 'pets'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    # child = backref relationship with cities
    child_id = db.Column(db.Integer, db.ForeignKey('children.id'), nullable=True)
    # parents = <relationship backref from User>

我想做这样的事情

parent_a = Parent()
child_a = Child()
pet_a = Pet()

然后我可以这样做

parent_a.children.append(child_a)
# commit/persist data
parent_a.children.all() # [child_a]

我想实现这样的目标

child_a.pets.append(pet_a)
parent_a.children.append(child_a)
# commit/persist data
parent_a.children.all() # [child_a]
parent_a.pets.all() # [pet_a], because pet_a gets
                    # automatically added to parent using some sorcery
                    # like for child in parent_a.children.all():
                    #     parent.pets.append(child.pets.all())
                    # or something like that.

我可以使用 Parent 对象中的一个方法来实现这一点，比如 add_child_and_its_pets() ，但我想覆盖关系的工作方式，所以我不需要覆盖可能受益于这种行为的其他模块，例如 Flask-Admin 。

基本上我应该如何覆盖 backref.append 方法或 relationship.append 方法，以便在调用时(即在 python 端)附加来自其他关系的其他对象？我应该如何覆盖 remove 方法？

最佳答案

对于 parent.pets.all() ，我认为您可以使用 children 作为 secondary join 条件，并将其视为 associative entity or junction table 。

这取决于您的表，但看起来像:

Parent.pets = relationship(
    Pet,
    backref='parent'
    primaryjoin=Pet.child_id == Child.id,
    secondaryjoin=Child.parent_id == Parent.id
)

如果您愿意，您也可以相当合理地制作一个 backref parent - 这将让您同时访问 parent_a.pets 和 pet_a.parent 。

关于python - 覆盖 sqlalchemy 中的关系行为，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50031376/