Re: [sqlalchemy] NEED HELP! Using Hybrid property in SQLAlchemy filter throws error
Hey thanks a ton! On Thu, Oct 22, 2020 at 8:26 PM Simon King wrote: > 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 > 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='siz
Re: [sqlalchemy] NEED HELP! Using Hybrid property in SQLAlchemy filter throws error
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 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, n
Re: [sqlalchemy] NEED HELP! Using Hybrid property in SQLAlchemy filter throws error
> > > 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__
Re: [sqlalchemy] NEED HELP! Using Hybrid property in SQLAlchemy filter throws error
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 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, 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 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/t
Re: [sqlalchemy] NEED HELP! Using Hybrid property in SQLAlchemy filter throws error
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, 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 > 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. ---
Re: [sqlalchemy] NEED HELP! Using Hybrid property in SQLAlchemy filter throws error
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 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 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/CAFHwexfvqQs6c%2BtR20pkojBt4JjToWB7gxW7U9O4Pb_gxpxC3A%40mail.gmail.com.
[sqlalchemy] NEED HELP! Using Hybrid property in SQLAlchemy filter throws error
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.