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 <[email protected]> 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 <[email protected]> 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 <[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 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/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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexeOqttoMT48Uw3H%2BKD3URk_XGEi-mVR7xD30o7ANBJk_A%40mail.gmail.com.
