Thanks for your input , the children have a many to many relationship with the parent FinishedGoods , with this how would I refer to parent_id , do i need to create and add that also ?
On Wed, 21 Oct 2020, 14:48 Simon King, <si...@simonking.org.uk> wrote: > The "expression" part of a hybrid property is used whenever you write > "FinishedGoodsParent.balance". It operates in the context of the > class, not a single instance, and it needs to return an SQL expression > that can be used inside a larger query. > > In your version, you are trying to iterate over "cls.children", but > that's not possible because "cls.children" is not a list. Hybrid > properties that work across relationships can be a bit difficult to > think about. The expression that you return needs to access another > table, so you need to consider how the query will join to that table: > > > https://docs.sqlalchemy.org/en/13/orm/extensions/hybrid.html#working-with-relationships > > In your case, you actually need to perform an aggregating function > (sum) on the related table. The easiest way to do that would be to > follow the correlated subquery example from the docs: > > > https://docs.sqlalchemy.org/en/13/orm/extensions/hybrid.html#correlated-subquery-relationship-hybrid > > Something like this: > > @balance.expression > def balance(cls): > return select([sa.func.sum(FinishedGoodsChild.qty)]).\ > where(FinishedGoodsChild.parent_id==cls.id).\ > label('balance') > > You probably need something a bit more complicated than that - I > didn't understand the join condition between parent and child in your > example so I made up the "parent_id" column. > > Hope that helps, > > Simon > > On Tue, Oct 20, 2020 at 4:57 PM Padam Sethia <padamseth...@gmail.com> > wrote: > > > > Hello , > > > > I'm having an issue with Hybrid methods - I still don't understand them > enough properly . So I have a parent and child many to many relationship > > > > > > This is the child model > > > > class FinishedGoodsChild(TimestampMixin, db.Model): > > id = db.Column(db.Integer, primary_key=True) > > hold_qty = db.Column(db.Float, default=0) > > pen_qty = db.Column(db.Float, default=0) > > qty = db.Column(db.Float, nullable=False, default=0) > > sku = db.Column(db.String(40), nullable=False, default='') > > size = db.relationship('SizeMaster', > secondary='size_goods_child', passive_deletes=True, > backref='size_goods_child', lazy='joined') > > size_id = db.Column(db.Integer, nullable=False, default=None) > > > > This is the Parent model > > > > > > class FinishedGoodsChild(TimestampMixin, db.Model): > > id = db.Column(db.Integer, primary_key=True) > > qty = db.Column(db.Float, default=0) > > balance = db.Column(db.Float) > > children = db.relationship('FinishedGoodsChild', > passive_deletes=True, secondary='goods_child_sizes', > backref='goods_child_sizes', lazy='joined') > > > > > > No I need to filter by the sum of the children qty > > > > > > Here is the hybrid property that I have set up , but throws not > implemented error > > > > > > @hybrid_property > > def balance(self): > > return sum(acc.qty for acc in self.children) @balance.expression > > def balance(cls): > > return sum(acc.qty for acc in cls.children) > > > > Help is much appreciated thanks! > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > > --- > > You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an email to sqlalchemy+unsubscr...@googlegroups.com. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/ba1add47-9497-4b92-8cb8-926e03c958a5n%40googlegroups.com > . > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/4_H-6lP3d_k/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfvqQs6c%2BtR20pkojBt4JjToWB7gxW7U9O4Pb_gxpxC3A%40mail.gmail.com > . > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOe5kwTV17P6cPbD9J86HfYt2kpRVn0v-m0frY7dYF7UpzQz-Q%40mail.gmail.com.