On 06/04/2010 03:13 PM, Kent wrote:
> Say I track Inventory with three classes: Product, Inventory, Location
>
> This any() expression yields the following output.
>
> session.query(Product).filter(Product.inventory.any(Location.siteid==u'EAST')).all()
>
> SELECT ...
> FROM products
> WHERE EXISTS (SELECT 1
> FROM inventory, locations
> WHERE products.productid = inventory.productid AND locations.siteid = %
> (siteid_1)s)
>
> What if I really need the inventory and locations tables to by JOINed.
>
> SELECT ...
> FROM products
> WHERE EXISTS (SELECT 1
> FROM inventory JOIN locations ON sqla_magic_clause <------------
> JOIN
> WHERE products.productid = inventory.productid AND locations.siteid = %
> (siteid_1)s)
>
> Can I get to this with the any() expression?
>
>
The quickest way is to add another any() clause. Assuming your
Inventory->Location relation is many-to-one or one-to-one (meaning you
would use has() instead of any()), you can use this query:
session.query(Product).filter(
Product.inventory.any(
Inventory.location.has(Location.siteid==u'EAST'))).all()
If you don't like nesting another EXISTS clause in your SQL, you can
create the inner query manually:
subq = session.query(Inventory)
subq = subq.join(Inventory.location)
subq = subq.filter(Inventory.productid == Product.productid)
subq = subq.filter(Location.siteid == u'EAST')
subq = subq.correlate(Product) # Probably not needed.
subq = subq.subquery()
session.query(Product).filter(exists(subq)).all()
-Conor
--
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.
