[sqlalchemy] Re: Self Join

2009-09-17 Thread Michael Bayer 

Paulo Aquino wrote:
 On Tue, Sep 15, 2009 at 5:53 AM, Michael Bayer
 mike...@zzzcomputing.comwrote:


 Paulo Aquino wrote:
  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

 you can say:

 valid_products = session.query(Product,
 ProductPrice.type).join(ProductPrice).filter(valid_price)

 a = valid_products.subquery()
 b = valid_products.subquery()

 PA = aliased(Product, a)
 PB = aliased(Product, b)

 q = session.query(PA, PB).\
distinct().\
filter(a.c.type=='Selling').\
filter(b.c.type=='Buying').\
filter(a.c.principal==b.c.principal).\
filter(a.c.sku==b.c.sku)


This works perfectly fine, but count() is wrong, seems to return a
 count
 which is more than 1 from what is expected.


dont use query.count() for queries like these.  use
query.value(func.count(somecolumn)).



--~--~-~--~~~---~--~~
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] Re: Self Join

2009-09-16 Thread Paulo Aquino 
On Tue, Sep 15, 2009 at 5:53 AM, Michael Bayer mike...@zzzcomputing.comwrote:


 Paulo Aquino wrote:
  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

 you can say:

 valid_products = session.query(Product,
 ProductPrice.type).join(ProductPrice).filter(valid_price)

 a = valid_products.subquery()
 b = valid_products.subquery()

 PA = aliased(Product, a)
 PB = aliased(Product, b)

 q = session.query(PA, PB).\
distinct().\
filter(a.c.type=='Selling').\
filter(b.c.type=='Buying').\
filter(a.c.principal==b.c.principal).\

   filter(a.c.sku==b.c.sku)





 print q.all()

 if you just want the columns back you can do away with PA and PB and just
 query(a, b).



 


--~--~-~--~~~---~--~~
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] Re: Self Join

2009-09-16 Thread Paulo Aquino 
On Tue, Sep 15, 2009 at 5:53 AM, Michael Bayer mike...@zzzcomputing.comwrote:


 Paulo Aquino wrote:
  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

 you can say:

 valid_products = session.query(Product,
 ProductPrice.type).join(ProductPrice).filter(valid_price)

 a = valid_products.subquery()
 b = valid_products.subquery()

 PA = aliased(Product, a)
 PB = aliased(Product, b)

 q = session.query(PA, PB).\
distinct().\
filter(a.c.type=='Selling').\
filter(b.c.type=='Buying').\
filter(a.c.principal==b.c.principal).\
filter(a.c.sku==b.c.sku)


   This works perfectly fine, but count() is wrong, seems to return a count
which is more than 1 from what is expected.


 print q.all()

 if you just want the columns back you can do away with PA and PB and just
 query(a, b).



 


--~--~-~--~~~---~--~~
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] Re: Self Join

2009-09-14 Thread Michael Bayer 

Paulo Aquino wrote:
 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

you can say:

valid_products = session.query(Product,
ProductPrice.type).join(ProductPrice).filter(valid_price)

a = valid_products.subquery()
b = valid_products.subquery()

PA = aliased(Product, a)
PB = aliased(Product, b)

q = session.query(PA, PB).\
distinct().\
filter(a.c.type=='Selling').\
filter(b.c.type=='Buying').\
filter(a.c.principal==b.c.principal).\
filter(a.c.sku==b.c.sku)

print q.all()

if you just want the columns back you can do away with PA and PB and just
query(a, b).



--~--~-~--~~~---~--~~
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] Re: Self Join via Join Table ?

2009-02-25 Thread az 

u mean, the Bar is an association table of Foo to Foo?
u have to use secondary_table and/or secondary_join in the relation 
setup. And probably specify remote_side or it may not know which Foo 
is what.

On Wednesday 25 February 2009 03:39:20 Stef wrote:
 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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Self Join via Join Table ?

2009-02-25 Thread Stephen Telford 
Hello Az,
Yes, Bar is the association table of Foo to Foo. In essence, this is a
self join through a join table.. I have tried and hit my head on this for
(quite literally) hours. In the end, and for the record, I ended up creating
a method on the model itself such as ;

def children(self):
childFoo=Foo.__table__.alias()
return
object_session(self).query(Foo).filter(and_(childFoo.c.deleted == 0,
childFoo.id  self.id, self.id == Bar.parent_id))

Not the prettiest way, nor what I would expect, but in lieu of an actual
example, and to help anyone who ends up treading the same path as me, I hope
this helps.

Regards
Stef

On Wed, Feb 25, 2009 at 4:29 AM, a...@svilendobrev.com wrote:


 u mean, the Bar is an association table of Foo to Foo?
 u have to use secondary_table and/or secondary_join in the relation
 setup. And probably specify remote_side or it may not know which Foo
 is what.

 On Wednesday 25 February 2009 03:39:20 Stef wrote:
  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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Self Join via Join Table ?

2009-02-25 Thread Michael Bayer 
check out the association proxy extension if you're looking to have  
Bar be hidden as an association object.   it will ultimately use  
Foo/Bar for querying but attribute access would be proxied through the  
names you confgure.


On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:

 Hello Az,
 Yes, Bar is the association table of Foo to Foo. In essence,  
 this is a self join through a join table.. I have tried and hit my  
 head on this for (quite literally) hours. In the end, and for the  
 record, I ended up creating a method on the model itself such as ;

 def children(self):
 childFoo=Foo.__table__.alias()
 return  
 object_session(self).query(Foo).filter(and_(childFoo.c.deleted == 0,  
 childFoo.id  self.id, self.id == Bar.parent_id))

 Not the prettiest way, nor what I would expect, but in lieu of  
 an actual example, and to help anyone who ends up treading the same  
 path as me, I hope this helps.

 Regards
 Stef

 On Wed, Feb 25, 2009 at 4:29 AM, a...@svilendobrev.com wrote:

 u mean, the Bar is an association table of Foo to Foo?
 u have to use secondary_table and/or secondary_join in the relation
 setup. And probably specify remote_side or it may not know which Foo
 is what.

 On Wednesday 25 February 2009 03:39:20 Stef wrote:
  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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Self Join via Join Table ?

2009-02-25 Thread Stephen Telford 
Okay, that sounds like a plan but., not to sound too much like a broken
record, does anyone have an -actual- example ? looking at pages with a lot
of API's is not really going to help me too much :(

This maybe slightly off-topic and it's really NOT meant as flamebait but.. I
remember a while ago playing around with DBIx (the perl ORM) and one of the
things that -really- made it easy to get to grips with as the
DBIx::Cookbook. It maybe a good idea to fling something similiar into the
sqlalchemy documentation...

if I had more experience I would write it but.. yes. It definitely is the
quickest ORM I have seen/used, but, all the speed is for naught if you hit
the 20% wall.

Regards
Stef

On Wed, Feb 25, 2009 at 4:21 PM, Michael Bayer mike...@zzzcomputing.comwrote:

 check out the association proxy extension if you're looking to have Bar
 be hidden as an association object.   it will ultimately use Foo/Bar for
 querying but attribute access would be proxied through the names you
 confgure.


 On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:

 Hello Az,
 Yes, Bar is the association table of Foo to Foo. In essence, this is a
 self join through a join table.. I have tried and hit my head on this for
 (quite literally) hours. In the end, and for the record, I ended up creating
 a method on the model itself such as ;

 def children(self):
 childFoo=Foo.__table__.alias()
 return
 object_session(self).query(Foo).filter(and_(childFoo.c.deleted == 0,
 childFoo.id  self.id, self.id == Bar.parent_id))

 Not the prettiest way, nor what I would expect, but in lieu of an
 actual example, and to help anyone who ends up treading the same path as me,
 I hope this helps.

 Regards
 Stef

 On Wed, Feb 25, 2009 at 4:29 AM, a...@svilendobrev.com wrote:


 u mean, the Bar is an association table of Foo to Foo?
 u have to use secondary_table and/or secondary_join in the relation
 setup. And probably specify remote_side or it may not know which Foo
 is what.

 On Wednesday 25 February 2009 03:39:20 Stef wrote:
  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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Self Join via Join Table ?

2009-02-25 Thread Bobby Impollonia 

I am doing something similar. The following code works for me in SQLA .4.8
class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)

bar_table = Table('bar', Base.metadata,
Column('parent_id', Integer, ForeignKey('foo.id'), nullable=False),
Column('child_id', Integer, ForeignKey('foo.id'), nullable=False))

Foo.children = relation(Foo, secondary = bar_table, primaryjoin =
bar_table.c.parent_id == Foo.id, secondaryjoin=bar_table.c.child_id ==
Foo.id, backref='parents')

On Wed, Feb 25, 2009 at 3:12 PM, Stephen Telford stelford1...@gmail.com wrote:
 Okay, that sounds like a plan but., not to sound too much like a broken
 record, does anyone have an -actual- example ? looking at pages with a lot
 of API's is not really going to help me too much :(

 This maybe slightly off-topic and it's really NOT meant as flamebait but.. I
 remember a while ago playing around with DBIx (the perl ORM) and one of the
 things that -really- made it easy to get to grips with as the
 DBIx::Cookbook. It maybe a good idea to fling something similiar into the
 sqlalchemy documentation...

 if I had more experience I would write it but.. yes. It definitely is the
 quickest ORM I have seen/used, but, all the speed is for naught if you hit
 the 20% wall.

 Regards
 Stef

 On Wed, Feb 25, 2009 at 4:21 PM, Michael Bayer mike...@zzzcomputing.com
 wrote:

 check out the association proxy extension if you're looking to have Bar
 be hidden as an association object.   it will ultimately use Foo/Bar for
 querying but attribute access would be proxied through the names you
 confgure.

 On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:

 Hello Az,
     Yes, Bar is the association table of Foo to Foo. In essence, this is a
 self join through a join table.. I have tried and hit my head on this for
 (quite literally) hours. In the end, and for the record, I ended up creating
 a method on the model itself such as ;

     def children(self):
     childFoo=Foo.__table__.alias()
     return
 object_session(self).query(Foo).filter(and_(childFoo.c.deleted == 0,
 childFoo.id  self.id, self.id == Bar.parent_id))

     Not the prettiest way, nor what I would expect, but in lieu of an
 actual example, and to help anyone who ends up treading the same path as me,
 I hope this helps.

     Regards
     Stef

 On Wed, Feb 25, 2009 at 4:29 AM, a...@svilendobrev.com wrote:

 u mean, the Bar is an association table of Foo to Foo?
 u have to use secondary_table and/or secondary_join in the relation
 setup. And probably specify remote_side or it may not know which Foo
 is what.

 On Wednesday 25 February 2009 03:39:20 Stef wrote:
  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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Self Join via Join Table ?

2009-02-25 Thread Stephen Telford 
Thank you Bobby!! That does make things more easier, and it shows then that
I am being a -real- moron here..

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker, mapper, relation

meta = MetaData()
meta.bind = 'postgres://root:sxta...@192.168.2.198/compass_master'

engine = create_engine('postgres://
root:mypassw...@192.168.2.198/compass_master', echo=True)
Session = sessionmaker(bind=engine)
session = Session()


trip_table = Table('trip', meta, autoload=True)
class Trip(object):
   id = Column(Integer, primary_key=True)
trip = mapper(Trip, trip_table)

trip_pc_table = Table('trip_parent_child', meta, autoload=True)
class TripParentChild(object):
   parent_id = Column(Integer, ForeignKey('Trip.id'), nullable=False)
   parent = relation(Trip)
   child_id = Column(Integer, ForeignKey('Trip.id'), nullable=False)
   child = relation(Trip)
trip_pc = mapper(TripParentChild, trip_pc_table)

Trip.children = relation(Trip, secondary = trip_table, primaryjoin =
TripParentChild.parent_id == Trip.id, secondaryjoin=TripParentChild.child_id
== Trip.id)
Trip.parent = relation(Trip, secondary = trip_table, primaryjoin =
TripParentChild.child_id == Trip.id, secondaryjoin=TripParentChild.parent_id
== Trip.id)

s = session.query(Trip).get(194143)
print s.children

results in a very nice stack trace;

Traceback (most recent call last):
  File ./s.py, line 37, in module
print s.children
  File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/properties.py,
line 628, in __str__
return str(self.parent.class_.__name__) + . + self.key
AttributeError: 'RelationProperty' object has no attribute 'parent'


Any thoughts ? I suspect some attempt at 'black magic' in regards to the
parent_id

Regards
Stef


On Wed, Feb 25, 2009 at 7:05 PM, Bobby Impollonia bob...@gmail.com wrote:


 I am doing something similar. The following code works for me in SQLA .4.8
 class Foo(Base):
__tablename__ = 'foo'
 id = Column(Integer, primary_key=True)

 bar_table = Table('bar', Base.metadata,
Column('parent_id', Integer, ForeignKey('foo.id'), nullable=False),
Column('child_id', Integer, ForeignKey('foo.id'), nullable=False))

 Foo.children = relation(Foo, secondary = bar_table, primaryjoin =
 bar_table.c.parent_id == Foo.id, secondaryjoin=bar_table.c.child_id ==
 Foo.id, backref='parents')

 On Wed, Feb 25, 2009 at 3:12 PM, Stephen Telford stelford1...@gmail.com
 wrote:
  Okay, that sounds like a plan but., not to sound too much like a broken
  record, does anyone have an -actual- example ? looking at pages with a
 lot
  of API's is not really going to help me too much :(
 
  This maybe slightly off-topic and it's really NOT meant as flamebait
 but.. I
  remember a while ago playing around with DBIx (the perl ORM) and one of
 the
  things that -really- made it easy to get to grips with as the
  DBIx::Cookbook. It maybe a good idea to fling something similiar into the
  sqlalchemy documentation...
 
  if I had more experience I would write it but.. yes. It definitely is the
  quickest ORM I have seen/used, but, all the speed is for naught if you
 hit
  the 20% wall.
 
  Regards
  Stef
 
  On Wed, Feb 25, 2009 at 4:21 PM, Michael Bayer mike...@zzzcomputing.com
 
  wrote:
 
  check out the association proxy extension if you're looking to have
 Bar
  be hidden as an association object.   it will ultimately use Foo/Bar
 for
  querying but attribute access would be proxied through the names you
  confgure.
 
  On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:
 
  Hello Az,
  Yes, Bar is the association table of Foo to Foo. In essence, this is
 a
  self join through a join table.. I have tried and hit my head on this
 for
  (quite literally) hours. In the end, and for the record, I ended up
 creating
  a method on the model itself such as ;
 
  def children(self):
  childFoo=Foo.__table__.alias()
  return
  object_session(self).query(Foo).filter(and_(childFoo.c.deleted == 0,
  childFoo.id  self.id, self.id == Bar.parent_id))
 
  Not the prettiest way, nor what I would expect, but in lieu of an
  actual example, and to help anyone who ends up treading the same path as
 me,
  I hope this helps.
 
  Regards
  Stef
 
  On Wed, Feb 25, 2009 at 4:29 AM, a...@svilendobrev.com wrote:
 
  u mean, the Bar is an association table of Foo to Foo?
  u have to use secondary_table and/or secondary_join in the relation
  setup. And probably specify remote_side or it may not know which Foo
  is what.
 
  On Wednesday 25 February 2009 03:39:20 Stef wrote:
   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

[sqlalchemy] Re: Self Join via Join Table ?

2009-02-25 Thread Stephen Telford 
and of course, both the passwords -are- the same (duh ;) .. the 'get()'
works fine (obviously ;)

Regards
Stef

On Wed, Feb 25, 2009 at 7:53 PM, Stephen Telford stelford1...@gmail.comwrote:

 Thank you Bobby!! That does make things more easier, and it shows then that
 I am being a -real- moron here..

 from sqlalchemy import *
 from sqlalchemy.orm import sessionmaker, mapper, relation

 meta = MetaData()
 meta.bind = 'postgres://root:sxta...@192.168.2.198/compass_master'

 engine = create_engine('postgres://
 root:mypassw...@192.168.2.198/compass_master', echo=True)
 Session = sessionmaker(bind=engine)
 session = Session()


 trip_table = Table('trip', meta, autoload=True)
 class Trip(object):
id = Column(Integer, primary_key=True)
 trip = mapper(Trip, trip_table)

 trip_pc_table = Table('trip_parent_child', meta, autoload=True)
 class TripParentChild(object):
parent_id = Column(Integer, ForeignKey('Trip.id'), nullable=False)
parent = relation(Trip)
child_id = Column(Integer, ForeignKey('Trip.id'), nullable=False)
child = relation(Trip)
 trip_pc = mapper(TripParentChild, trip_pc_table)

 Trip.children = relation(Trip, secondary = trip_table, primaryjoin =
 TripParentChild.parent_id == Trip.id, secondaryjoin=TripParentChild.child_id
 == Trip.id)
 Trip.parent = relation(Trip, secondary = trip_table, primaryjoin =
 TripParentChild.child_id == Trip.id, secondaryjoin=TripParentChild.parent_id
 == Trip.id)

 s = session.query(Trip).get(194143)
 print s.children

 results in a very nice stack trace;

 Traceback (most recent call last):
   File ./s.py, line 37, in module
 print s.children
   File
 /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/properties.py,
 line 628, in __str__
 return str(self.parent.class_.__name__) + . + self.key
 AttributeError: 'RelationProperty' object has no attribute 'parent'


 Any thoughts ? I suspect some attempt at 'black magic' in regards to the
 parent_id

 Regards
 Stef



 On Wed, Feb 25, 2009 at 7:05 PM, Bobby Impollonia bob...@gmail.comwrote:


 I am doing something similar. The following code works for me in SQLA .4.8
 class Foo(Base):
__tablename__ = 'foo'
 id = Column(Integer, primary_key=True)

 bar_table = Table('bar', Base.metadata,
Column('parent_id', Integer, ForeignKey('foo.id'), nullable=False),
Column('child_id', Integer, ForeignKey('foo.id'), nullable=False))

 Foo.children = relation(Foo, secondary = bar_table, primaryjoin =
 bar_table.c.parent_id == Foo.id, secondaryjoin=bar_table.c.child_id ==
 Foo.id, backref='parents')

 On Wed, Feb 25, 2009 at 3:12 PM, Stephen Telford stelford1...@gmail.com
 wrote:
  Okay, that sounds like a plan but., not to sound too much like a broken
  record, does anyone have an -actual- example ? looking at pages with a
 lot
  of API's is not really going to help me too much :(
 
  This maybe slightly off-topic and it's really NOT meant as flamebait
 but.. I
  remember a while ago playing around with DBIx (the perl ORM) and one of
 the
  things that -really- made it easy to get to grips with as the
  DBIx::Cookbook. It maybe a good idea to fling something similiar into
 the
  sqlalchemy documentation...
 
  if I had more experience I would write it but.. yes. It definitely is
 the
  quickest ORM I have seen/used, but, all the speed is for naught if you
 hit
  the 20% wall.
 
  Regards
  Stef
 
  On Wed, Feb 25, 2009 at 4:21 PM, Michael Bayer 
 mike...@zzzcomputing.com
  wrote:
 
  check out the association proxy extension if you're looking to have
 Bar
  be hidden as an association object.   it will ultimately use Foo/Bar
 for
  querying but attribute access would be proxied through the names you
  confgure.
 
  On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:
 
  Hello Az,
  Yes, Bar is the association table of Foo to Foo. In essence, this
 is a
  self join through a join table.. I have tried and hit my head on this
 for
  (quite literally) hours. In the end, and for the record, I ended up
 creating
  a method on the model itself such as ;
 
  def children(self):
  childFoo=Foo.__table__.alias()
  return
  object_session(self).query(Foo).filter(and_(childFoo.c.deleted == 0,
  childFoo.id  self.id, self.id == Bar.parent_id))
 
  Not the prettiest way, nor what I would expect, but in lieu of an
  actual example, and to help anyone who ends up treading the same path
 as me,
  I hope this helps.
 
  Regards
  Stef
 
  On Wed, Feb 25, 2009 at 4:29 AM, a...@svilendobrev.com wrote:
 
  u mean, the Bar is an association table of Foo to Foo?
  u have to use secondary_table and/or secondary_join in the relation
  setup. And probably specify remote_side or it may not know which Foo
  is what.
 
  On Wednesday 25 February 2009 03:39:20 Stef wrote:
   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

[sqlalchemy] Re: Self Join via Join Table ?

2009-02-25 Thread Michael Bayer 
page 199 of the SQLAlchemy oreilly book talks about association proxy ;)



On Feb 25, 2009, at 6:12 PM, Stephen Telford wrote:

 Okay, that sounds like a plan but., not to sound too much like a  
 broken record, does anyone have an -actual- example ? looking at  
 pages with a lot of API's is not really going to help me too much :(

 This maybe slightly off-topic and it's really NOT meant as flamebait  
 but.. I remember a while ago playing around with DBIx (the perl ORM)  
 and one of the things that -really- made it easy to get to grips  
 with as the DBIx::Cookbook. It maybe a good idea to fling something  
 similiar into the sqlalchemy documentation...

 if I had more experience I would write it but.. yes. It definitely  
 is the quickest ORM I have seen/used, but, all the speed is for  
 naught if you hit the 20% wall.

 Regards
 Stef

 On Wed, Feb 25, 2009 at 4:21 PM, Michael Bayer mike...@zzzcomputing.com 
  wrote:
 check out the association proxy extension if you're looking to have  
 Bar be hidden as an association object.   it will ultimately use  
 Foo/Bar for querying but attribute access would be proxied through  
 the names you confgure.


 On Feb 25, 2009, at 4:11 PM, Stephen Telford wrote:

 Hello Az,
 Yes, Bar is the association table of Foo to Foo. In essence,  
 this is a self join through a join table.. I have tried and hit my  
 head on this for (quite literally) hours. In the end, and for the  
 record, I ended up creating a method on the model itself such as ;

 def children(self):
 childFoo=Foo.__table__.alias()
 return  
 object_session(self).query(Foo).filter(and_(childFoo.c.deleted ==  
 0, childFoo.id  self.id, self.id == Bar.parent_id))

 Not the prettiest way, nor what I would expect, but in lieu of  
 an actual example, and to help anyone who ends up treading the same  
 path as me, I hope this helps.

 Regards
 Stef

 On Wed, Feb 25, 2009 at 4:29 AM, a...@svilendobrev.com wrote:

 u mean, the Bar is an association table of Foo to Foo?
 u have to use secondary_table and/or secondary_join in the relation
 setup. And probably specify remote_side or it may not know which Foo
 is what.

 On Wednesday 25 February 2009 03:39:20 Stef wrote:
  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
-~--~~~~--~~--~--~---