On Tue, Sep 15, 2009 at 5:53 AM, Michael Bayer <mike...@zzzcomputing.com>wrote:
> > 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 -~----------~----~----~----~------~----~------~--~---
