Re: [sqlalchemy] Self-join and autoload
On Mar 28, 2014, at 6:40 AM, Simon King wrote:
> The alternative is to define the "children" relationship after the
> class has been defined:
>
> class PhotoObj(Base):
>
>__tablename__ = 'photoobj'
>__table_args__ = {'autoload':True, 'schema':'sdssphoto'}
>
> PhotoObj.children = relationship(PhotoObj, backref=backref('parent',
> remote_side=[PhotoObj.pk]))
Yes, that works perfectly. Odd - that was one of my permutations at one point!
I must have had something else going on.
Thanks!
Demitri
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Self-join and autoload
On Fri, Mar 28, 2014 at 1:16 AM, wrote:
> Hi,
>
> I'm trying to configure a table with autoload but can't quite get the syntax
> to set up a self-relationship. This is my abbreviated) schema:
>
> CREATE TABLE sdssphoto.photoobj
> (
> pk bigint NOT NULL DEFAULT nextval('photoobj_pk_seq'::regclass),
> parent_photoobj_pk bigint
> CONSTRAINT photoobj_pk PRIMARY KEY (pk),
>CONSTRAINT parent_fk FOREIGN KEY (parent_photoobj_pk)
> REFERENCES sdssphoto.photoobj (pk) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> )
>
> and my code:
>
> class PhotoObj(Base):
>
> __tablename__ = 'photoobj'
> __table_args__ = {'autoload':True, 'schema':'sdssphoto'}
>
> children = relationship('PhotoObj', backref=backref('parent',
> remote_side=[PhotoObj.pk]))
>
> The error I get is "NameError: name 'PhotoObj' is not defined". I've tried
> many iterations, but can't quite seem to get this right. Any suggestions
> would be appreciated!
>
In Python, you can't refer to a class while it is being defined. In
this instance, you are using the bare name PhotoObj in the remote_side
parameter to your backref.
I *think* (but haven't tested) that you should be able to specify the
remote_side parameter as a string:
children = relationship('PhotoObj', backref=backref('parent',
remote_side='[PhotoObj.pk]'))
The alternative is to define the "children" relationship after the
class has been defined:
class PhotoObj(Base):
__tablename__ = 'photoobj'
__table_args__ = {'autoload':True, 'schema':'sdssphoto'}
PhotoObj.children = relationship(PhotoObj, backref=backref('parent',
remote_side=[PhotoObj.pk]))
Hope that helps,
Simon
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Self-join and autoload
Hi,
I'm trying to configure a table with autoload but can't quite get the syntax to
set up a self-relationship. This is my abbreviated) schema:
CREATE TABLE sdssphoto.photoobj
(
pk bigint NOT NULL DEFAULT nextval('photoobj_pk_seq'::regclass),
parent_photoobj_pk bigint
CONSTRAINT photoobj_pk PRIMARY KEY (pk),
CONSTRAINT parent_fk FOREIGN KEY (parent_photoobj_pk)
REFERENCES sdssphoto.photoobj (pk) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
)
and my code:
class PhotoObj(Base):
__tablename__ = 'photoobj'
__table_args__ = {'autoload':True, 'schema':'sdssphoto'}
children = relationship('PhotoObj', backref=backref('parent',
remote_side=[PhotoObj.pk]))
The error I get is "NameError: name 'PhotoObj' is not defined". I've tried many
iterations, but can't quite seem to get this right. Any suggestions would be
appreciated!
Cheers,
Demitri
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Self Join
I have 2 tables 'Product' and 'ProductPrice'. I want to get all valid products, a product is valid if it has both a valid 'Selling' and 'Buying' ProductPrice type. A ProductPrice is valid if the valid_from date <= date.today() and valid_to >= date.today(). Product Table: id | sku | principal +---+--- 1 | sku_1 | kraft 2 | sku_2 | kraft 3 | sku_3 | kraft 4 | sku_4 | kraft ProdutPrice Table: id | type| sku| principal | price | valid_from | valid_to +---+-+-++-+ 1 | Buying | sku_1 | kraft | 122 | 2009-05-05 | 2009-12-12 2 | Buying | sku_1 | kraft | 231 | 2010-01-01 | 2010-02-02 3 | Selling | sku_1 | kraft | 125 | 2009-05-05 | 2009-12-12 4 | Buying | sku_2 | kraft | 122 | 2009-05-05 | 2009-12-12 5 | Buying | sku_2 | kraft | 231 | 2010-01-01 | 2010-02-02 6 | Selling | sku_2 | kraft | 125 | 2009-05-05 | 2009-12-12 7 | Buying | sku_3 | kraft | 122 | 2009-05-05 | 2009-12-12 8 | Selling | sku_4 | kraft | 122 | 2009-05-05 | 2009-12-12 Using Raw SQL here is how I did it: 1. SELECT product.id, type, product.sku, product.principal, price, valid_from, valid_to INTO TEMP FROM product INNER JOIN product_price on product.principal = product_price.principal AND product.sku = product_price.sku WHERE valid_from <= current_date AND valid_to >= current_date ; 2. SELECT DISTINCT * from TEMP a , TEMP b WHERE a.type='Selling' AND b.type='Buying' AND a.principal = b.principal AND a.sku = b.sku; >From this two queries I now have distinct products that have a valid pair of both 'Buying' and 'Selling' Price. (Those products with a valid 'Buying' or 'Selling price only and not have them both are dropped) Using SQLAlchemy here is how I did it: 1. valid_price = and_(ProductPrice.sku == Product.sku, ProductPrice.principal==Product.principal, ProductPrice.valid_from <= date.today(), ProductPrice.valid_to >= date.today()) valid_products = session.query(Product).join(ProductPrice).filter(valid_price) 2. Now I want to self join valid_products, same thing I did in my Raw SQL solution no. 2 I've been trying but getting weird results. If someone can please help me, here's my test case http://pastebin.com/m3f8a95c8 rgds, Paulo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Self Join
I have 2 tables 'Product' and 'ProductPrice'. I want to get all valid products, a product is valid if it has both a valid 'Selling' and 'Buying' ProductPrice type. A ProductPrice is valid if the valid_from date <= date.today() and valid_to >= date.today(). Product Table: id | sku | principal +---+--- 1 | sku_1 | kraft 2 | sku_2 | kraft 3 | sku_3 | kraft 4 | sku_4 | kraft ProdutPrice Table: id | type| sku| principal | price | valid_from | valid_to +---+-+-++-+ 1 | Buying | sku_1 | kraft | 122 | 2009-05-05 | 2009-12-12 2 | Buying | sku_1 | kraft | 231 | 2010-01-01 | 2010-02-02 3 | Selling | sku_1 | kraft | 125 | 2009-05-05 | 2009-12-12 4 | Buying | sku_2 | kraft | 122 | 2009-05-05 | 2009-12-12 5 | Buying | sku_2 | kraft | 231 | 2010-01-01 | 2010-02-02 6 | Selling | sku_2 | kraft | 125 | 2009-05-05 | 2009-12-12 7 | Buying | sku_3 | kraft | 122 | 2009-05-05 | 2009-12-12 8 | Selling | sku_4 | kraft | 122 | 2009-05-05 | 2009-12-12 Using Raw SQL here is how I did it: 1. SELECT product.id, type, product.sku, product.principal, price, valid_from, valid_to INTO TEMP FROM product INNER JOIN product_price on product.principal = product_price.principal AND product.sku = product_price.sku WHERE valid_from <= current_date AND valid_to >= current_date ; 2. SELECT DISTINCT * from TEMP a , TEMP b WHERE a.type='Selling' AND b.type='Buying' AND a.principal = b.principal AND a.sku = b.sku; >From this two queries I now have distinct products that have a valid pair of both 'Buying' and 'Selling' Price. (Those products with a valid 'Buying' or 'Selling price only and not have them both are dropped) Using SQLAlchemy here is how I did it: 1. valid_price = and_(ProductPrice.sku == Product.sku, ProductPrice.principal==Product.principal, ProductPrice.valid_from <= date.today(), ProductPrice.valid_to >= date.today()) valid_products = session.query(Product).join(ProductPrice).filter(valid_price) 2. Now I want to self join valid_products, same thing I did in my Raw SQL solution no. 2 I've been trying but getting weird results. If someone can please help me, here's is my test case http://pastebin.com/m3f8a95c8 rgds, Paulo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Self Join via Join Table ?
Hello Everyone,
First of all, kudos on SQLAlchemy.. the speed is pretty amazing - I
am coming from the SQLObject world and there is a definite difference.
Excellent work. I am also getting to grips with it pretty quickly,
using object_session and all that good stuff. This said, I have hit
that 20% problem, and am hoping someone can shine a light on it.
I have a table, lets call it Foo and another table Bar. Foo should
be able to get a list of it's parents via Bar or it's children via
Bar. I am also using the declarative_base system rather than table/
mapper defined seperately.
class Foo(Base):
id = Column(Integer, primary_key=True)
class Bar(Base):
parent_id = Column(Integer, default=0)
child_id = Column(Integer, default=0)
So, I thought something like ; children = relation(Foo,
backref=backref('parents'), primaryjoin=and_(Foo.id==Bar.parent_id)
But that's where I hit the 'wall' as it were, is there a way to
setup a synonym for Foo in the primaryjoin clause ? Am I missing
something stupid ? (I am okay with that ;)
Regards
Stef
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
