NEED HELP! Using Hybrid property in SQLAlchemy filter throws error

[Padam Sethia]

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!

[Simon King]

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

> --
> 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+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ba1add47-9497-4b92-8cb8-926e03c958a5n%40googlegroups.com.

[Padam Sethia]

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 ?

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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexfvqQs6c%2BtR20pkojBt4JjToWB7gxW7U9O4Pb_gxpxC3A%40mail.gmail.com.

[Simon King]

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

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOe5kwTV17P6cPbD9J86HfYt2kpRVn0v-m0frY7dYF7UpzQz-Q%40mail.gmail.com.

[Padam Sethia]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexdPMkEcg3ujiH8sWz6rDgztJLose6LPC9wsJttu4SQ5gg%40mail.gmail.com.

[Simon King]

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

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOe5kwQh1S1tPS4Dw2RufoP-Up_1bCZr8MUtAgMS8vwEYHJzSw%40mail.gmail.com.

[Padam Sethia]

Hey thanks a ton! 

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexeOqttoMT48Uw3H%2BKD3URk_XGEi-mVR7xD30o7ANBJk_A%40mail.gmail.com.