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')
Re: [sqlalchemy] NEED HELP! Using Hybrid property in SQLAlchemy filter throws error
r, 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 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
> 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 Fi
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.c
[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.
[sqlalchemy] SQLAlchemy Filter Table using Model Methods
Hi , I want to filter my table using Model methods . So I have a method called get_gen_name() that gives me the actual item name from a combination of values in the tables & I want to search that . Any idea how do I achieve that ? I’m understand that there are hybrid properties / column expressions for this. I tried that . But the problem is what I am trying to search doesn't exist exclusively in the table , the item name is created on the fly from a combination of values in the table . I'm not sure how to get the hybrid props work for me in this case. Any 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/81910831-a48b-4575-bfff-3054c9e5f747o%40googlegroups.com.
[sqlalchemy] Unique Constraint in a Table over columns with multiple many to many relationships fails. How to fix ?
Hey Guys !
So as the title says it all , I'm try to do the above , but I'm getting SQL
Syntax error when I'm using db.Index to create a unique index . I've tried
all the solutions but nothing seems to work.
P.S : I'm using Flask-SQLalchemy with MySQL.
Here's my table
class FinGoods(db.Model):
id = db.Column(db.Integer , primary_key = True)
product_category = db.relationship('FinCat' ,cascade="all,delete",
secondary='cat_goods' ,
backref='cat_goods' , lazy = 'joined')
fabric_combo = db.relationship('FabComb' ,cascade="all,delete",
secondary='comb_goods' ,
backref='comb_goods' , lazy = 'joined')
print_tech = db.relationship('PrintTech' ,cascade="all,delete",
secondary='tech_goods' ,
backref='print_goods' , lazy = 'joined')
design = db.relationship('FinDes' ,cascade="all,delete",
secondary='des_goods' ,
backref='des_goods' , lazy = 'joined')
uom = db.relationship('Uom' ,cascade="all,delete", secondary='uom_goods' ,
backref='uom_goods' ,
lazy = 'joined')
alt_name = db.Column(db.String(200))
gen_name = db.Column(db.String(100))
def get_gen_name(self):
product_category = self.product_category
fabric_combo = self.fabric_combo
print_tech = self.print_tech
design = self.design
uom = self.uom
display_name = "{} / {} / {} / {}".format(product_category[0].cat,
fabric_combo[0].comb,
print_tech[0].tech, design[0].des)
return display_name
db.Table('cat_goods',
db.Column('cat_id' , db.Integer , db.ForeignKey('fin_cat.id' ,
ondelete='SET NULL' )),
db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' ,
ondelete='SET NULL'))
)
db.Table('comb_goods',
db.Column('comb_id' , db.Integer , db.ForeignKey('fab_comb.id' ,
ondelete='SET NULL' )),
db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' ,
ondelete='SET NULL'))
)
db.Table('tech_goods',
db.Column('tech_id' , db.Integer , db.ForeignKey('print_tech.id' ,
ondelete='SET NULL' )),
db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' ,
ondelete='SET NULL'))
)
db.Table('des_goods',
db.Column('des_id' , db.Integer , db.ForeignKey('fin_des.id' ,
ondelete='SET NULL' )),
db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' ,
ondelete='SET NULL'))
)
db.Table('uom_goods',
db.Column('uom_id' , db.Integer , db.ForeignKey('uom.id' , ondelete='SET
NULL' )),
db.Column('goods_id' , db.Integer , db.ForeignKey('fin_goods.id' ,
ondelete='SET NULL'))
)
Here's the code for the unique Index :
db.Index('fin_goods_unq', cat_goods.cat_id,
fin_goods.fabric_combo,fin_goods.print_tech , fin_goods.design, fin_goods.uom,
unique=True)
But it's throwing a SQL Syntax error , which I'm unable to wrap my head
around.
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You
have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '= raw_fab_id AND id =
cat_id, id = raw_fab_id AND id = comb_id, id = raw_fab_id ' at line 1")
[SQL: CREATE UNIQUE INDEX raw_goods_unq ON raw_fab_main (id = raw_fab_id AND id
= cat_id, id = raw_fab_id AND id = comb_id, id = raw_fab_id AND id = const_id,
id = raw_fab_id AND id = proc_id, id = raw_fab_id AND id = width_id, id =
raw_fab_id AND id = dye_id)]
(Background on this error at: http://sqlalche.me/e/f405)
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/28cf95ee-8cf8-48b5-a120-0ffcad64368d%40googlegroups.com.
