曾孙的SQLAlchemy链接协会代理吗? [英] SQLAlchemy chaining association proxy for great grandchildren?

问题描述

我有四个类，例如:Group，Parent，Child，Toy.

I have a four classes like so: Group, Parent, Child, Toy.

• Group具有指向Parent
的parents关系
• Parent具有指向Child
的children关系
• Child具有指向Toy
的toys关系

• Group has a parents relationship pointing to Parent
• Parent has a children relationship pointing to Child
• Child has a toys relationship pointing to Toy

Parent的toys association_proxy生成Parent子级具有的所有Toy.

Parent has a toys association_proxy that produces all the Toys that the Parent's children have.

我希望能够将所有玩具归为一组.我试图在Group上创建一个链接到Parent的toys的association_proxy，但是会生成以下内容:

I want to be able to get all the Toys in a Group. I tried to create an association_proxy on Group that links to Parent's toys, but it produces this:

[[<Toy 1>, <Toy 2>], [], [], []]

当我想要这个时:

[<Toy 1>, <Toy 2>]

如果Parent的children没有任何Toy，则toys关联代理为[].但是，第二个关联代理不知道要排除空列表.此外，列表应折叠.反正有办法使它正常工作吗?

If the Parent's children don't have any Toys, then the toys association proxy is []. However, the second association proxy doesn't know to exclude the empty lists. Also, the lists should be collapsed. Is there anyway to get this to work?

class Group(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    created_at = db.Column(db.DateTime, default=utils.get_now_datetime)
    name = db.Column(db.String(80, convert_unicode=True))
    # relationships
    parents = db.relationship('Parent', backref='group')
    toys = association_proxy('parents', 'toys')

class Parent(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    group_id = db.Column(db.Integer, db.ForeignKey('group.id', ondelete='CASCADE'))
    created_at = db.Column(db.DateTime, default=utils.get_now_datetime)
    first_name = db.Column(db.String(80, convert_unicode=True))
    last_name = db.Column(db.String(80, convert_unicode=True))
    children = db.relationship('Child', backref='parent', cascade='all, delete')
    toys = association_proxy('children', 'toys')

class Child(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('parent.id', ondelete='CASCADE'))
    created_at = db.Column(db.DateTime, default=utils.get_now_datetime)

class Toy(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    child_id = db.Column(db.Integer, db.ForeignKey('child.id', ondelete='CASCADE'))
    created_at = db.Column(db.DateTime, default=utils.get_now_datetime)
    child = db.relationship('Child', backref=db.backref("toys",cascade="all, delete-orphan", order_by="desc(Toy.id)"))

推荐答案

鉴于这些仅用于检索和查看(如您在评论中所述，添加将是模棱两可的)，我宁愿进行viewonly关系没有和association_proxy:

Given that those are for retrieval and view only (as you mentioned in the comment, adding would be ambiguous), I would rather do a viewonly relationship without an association_proxy:

class Group(db.Model):
    # ...
    toys = relationship('Toy',
        secondary="join(Group, Parent, Group.id == Parent.group_id).join(Child, Parent.id == Child.parent_id)",
        primaryjoin="and_(Group.id == Parent.group_id, Parent.id == Child.parent_id)",
        secondaryjoin="Child.id == Toy.child_id",
        viewonly=True,
    )

请注意，这是SQLAlchemy的新功能，在文档的Composite Secondary Joins 部分.

然后您可以将其仅用于查询:

Then you can use it just for query:

group_id = 123
group = session.query(Group).get(group_id)
print(group.toys)

或者您甚至可以使用它进行过滤，因此要查找包含名称为超级马里奥"的玩具的玩具组，您可以执行以下操作:

Or you can even use it to filter, so to find a group which contains a toy with name "Super Mario" you can do:

group = session.query(Group).filter(Group.toys.any(Toy.name == "Super Mario"))

---

但是实际上，您可以使用简单查询或创建启用查询的属性来完成所有这些操作.请参阅文档的自定义列属性部分，您可以在其中使用任何简单属性column_property或hybrid attribute.

---

But in reality all this you can do with simple query, or create a query-enabled property. See Customizing Column Properties section of the documentation, where you can use any of the simple property, column_property or hybrid attribute.

这篇关于曾孙的SQLAlchemy链接协会代理吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！