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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
