You don't need to add a parent_id column, you just need to write the full relationship condition inside that "select" statement. It would be much easier to explain if you can show your actual parent and child classes, including the relationship between them and the association table.
Simon On Thu, Oct 22, 2020 at 7:23 AM Padam Sethia <[email protected]> wrote: > > 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, <[email protected]> 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 <[email protected]> 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 [email protected]. >> > 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 >> [email protected]. >> 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAOe5kwTV17P6cPbD9J86HfYt2kpRVn0v-m0frY7dYF7UpzQz-Q%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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexdPMkEcg3ujiH8sWz6rDgztJLose6LPC9wsJttu4SQ5gg%40mail.gmail.com.
