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',
> >>
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,
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)
>
>
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
>>
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.
