> > > 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.