So FinishedGoods is connected to FinishedGoodsChild through the goods_child_sizes table? You haven't provided a definition of that table, so I'm going to guess it's something like this:
goods_child_sizes = db.Table( 'goods_child_sizes', db.Column('parent_id', db.Integer, db.ForeignKey('finished_goods.id'), primary_key=True), db.Column('child_id', db.Integer, db.ForeignKey('finished_goods_child.id'), primary_key=True), ) If so, then your hybrid property would look something like: @balance.expression def balance(cls): condition = sa.and_( FinishedGoodsChild.id == goods_child_sizes.c.child_id, goods_child_sizes.c.parent_id == cls.id, ) return select([sa.func.sum(FinishedGoodsChild.qty)]).\ where(condition).\ label('balance') (You'll have to fix those column names to match your real goods_child_sizes table) Simon On Thu, Oct 22, 2020 at 2:41 PM Padam Sethia <padamseth...@gmail.com> wrote: >> >> >> 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) >> >> def __init__(self, qty, sku, size): >> self.qty = qty >> self.sku = sku >> self.size_id = size.id >> self.size.append(size) >> >> >> db.Table('size_goods_child', >> >> db.Column('size_id', db.Integer, db.ForeignKey( >> 'size_master.id', ), primary_key=True, nullable=False >> ), >> db.Column('goods_id', db.Integer, db.ForeignKey( >> 'finished_goods_child.id', ), primary_key=True, nullable=False >> ) >> ) >> >> >> class FinishedGoodsChildSchema(ma.ModelSchema): >> id = field_for(FinishedGoodsChild, 'id', dump_only=True) >> qty = field_for(FinishedGoodsChild, 'qty', dump_only=True) >> hold_qty = field_for(FinishedGoodsChild, 'hold_qty', dump_only=True) >> pen_qty = field_for(FinishedGoodsChild, 'pen_qty', dump_only=True) >> sku = field_for(FinishedGoodsChild, 'sku', dump_only=True) >> >> size = ma.Nested(SizeMasterSchema, many=True) >> >> class meta: >> model = FinishedGoodsChild >> >> >> db.Table('hsn_goods', >> db.Column('hsn_id', db.Integer, db.ForeignKey( >> 'hsn.id', ), primary_key=True, nullable=False >> ), >> db.Column('goods_id', db.Integer, db.ForeignKey( >> 'finished_goods.id', ), primary_key=True, nullable=False >> ) >> ) >> >> class FinishedGoods(SearchableMixin, TimestampMixin, db.Model): >> __searchable__ = ['balance','created', 'updated', 'title', >> 'description', 'sku','qty', >> 'product_category', 'fabric_combination', >> 'print_technique', 'design_number'] >> >> id = db.Column(db.Integer, primary_key=True) >> >> filter_tags = db.relationship( >> 'FilterTags', passive_deletes=True, secondary='filter_tags_goods', >> backref='filter_tags_goods', lazy='joined') >> product_category = db.relationship( >> 'ProductCategory', passive_deletes=True, >> secondary='product_category_goods', backref='product_category_goods', >> lazy='joined') >> fabric_combination = db.relationship( >> 'FabricCombination', passive_deletes=True, >> secondary='fabric_combination_goods', backref='fabric_combination_goods', >> lazy='joined') >> print_technique = db.relationship( >> 'PrintTechnique', passive_deletes=True, >> secondary='print_technique_goods', backref='print_technique_goods', >> lazy='joined') >> design_number = db.relationship( >> 'DesignNumber', passive_deletes=True, >> secondary='design_number_goods', backref='design_number_goods', >> lazy='joined') >> uom = db.relationship('Uom', >> secondary='uom_goods', passive_deletes=True, >> backref='uom_goods', lazy='joined') >> hsn = db.relationship('Hsn', >> secondary='hsn_goods', passive_deletes=True, >> backref='hsn_goods', lazy='joined') >> size = db.relationship('SizeMaster', >> secondary='size_goods', passive_deletes=True, >> backref='size_goods', lazy='joined') >> >> size_chart = db.relationship('SizeChart', >> secondary='size_chart_goods', >> passive_deletes=True, backref='size_chart_goods', lazy='joined') >> >> # Foreign Key IDs for Unique Constraint >> >> product_category_id = db.Column(db.Integer, nullable=False) >> fabric_combination_id = db.Column(db.Integer, nullable=False) >> print_technique_id = db.Column(db.Integer, nullable=False) >> design_number_id = db.Column(db.Integer, nullable=False) >> uom_id = db.Column(db.Integer, nullable=False) >> size_id = db.Column(db.Integer, nullable=False, default=None) >> # End FK >> title = db.Column(db.String(100), nullable=False) >> description = db.Column(db.String(250), nullable=False) >> >> price = db.Column(db.Float, nullable=False, default="0") >> qty = db.Column(db.Float, nullable=False) >> hold_qty = db.Column(db.Float, default=0) >> pen_qty = db.Column(db.Float, default=0) >> >> gst = db.Column(db.Integer, nullable=False, default="0") >> multiple = db.Column(db.Integer, nullable=False, default=1) >> sku = db.Column(db.String(40), nullable=False) >> >> image = db.Column(db.String(250)) >> >> children = db.relationship('FinishedGoodsChild', >> passive_deletes=True, >> secondary='goods_child_sizes', backref='goods_child_sizes', lazy='joined') >> balance = db.Column(db.Float , default= 0) >> >> __table_args__ = (db.UniqueConstraint( >> 'product_category_id', 'fabric_combination_id', >> 'print_technique_id', 'design_number_id', 'sku', >> name='finished_goods_chk_id'), ) >> >> def __init__(self, product_category, fabric_combination, >> print_technique, design_number, uom, size, title, description, sku, price, >> qty, multiple): >> self.product_category_id = product_category.id >> self.product_category.append(product_category) >> >> self.fabric_combination_id = fabric_combination.id >> self.fabric_combination.append(fabric_combination) >> >> self.print_technique_id = print_technique.id >> self.print_technique.append(print_technique) >> >> self.design_number_id = design_number.id >> self.design_number.append(design_number) >> >> self.uom_id = uom.id >> self.uom.append(uom) >> >> self.size_id = size.id >> self.size.append(size) >> >> self.title = title >> self.description = description >> self.price = price >> self.qty = qty >> self.multiple = multiple >> self.sku = sku >> >> def get_gen_name(self): >> goods_name = "{}/{}/{}/{}".format( >> self.product_category[0].name, self.fabric_combination[0].name, >> self.print_technique[0].name, self.design_number[0].name) >> return goods_name > > > On Thu, Oct 22, 2020 at 3:53 PM Simon King <si...@simonking.org.uk> wrote: >> >> 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 <padamseth...@gmail.com> 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, <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. >> >> -- >> 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/CAFHwexdPMkEcg3ujiH8sWz6rDgztJLose6LPC9wsJttu4SQ5gg%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/CAOe5kwQh1S1tPS4Dw2RufoP-Up_1bCZr8MUtAgMS8vwEYHJzSw%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/CAFHwexeOqttoMT48Uw3H%2BKD3URk_XGEi-mVR7xD30o7ANBJk_A%40mail.gmail.com.