Nice. That might come in very useful, thanks.
However, I can't quite get the second approach to work:
exq=DBSession.query(Inventory).join(Location).filter(Location.siteid=='03').correlate(Product).subquery()
DBSession.query(Product).filter(exists(exq)).all()
Traceback (most recent call last):
File "<console>", line 1, in <module>
File
"/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/sql/expression.py",
line 512, in exists
return _Exists(*args, **kwargs)
File
"/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/sql/expression.py",
line 2806, in __init__
s = select(*args, **kwargs).as_scalar().self_group()
File
"/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/sql/expression.py",
line 237, in select
return Select(columns, whereclause=whereclause, from_obj=from_obj,
**kwargs)
File
"/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6-linux-x86_64.egg/sqlalchemy/sql/expression.py",
line 3637, in __init__
raise exc.ArgumentError("columns argument to select() must "
ArgumentError: columns argument to select() must be a Python list or
other iterable
print exq
SELECT inventory.productid, inventory.siteid, inventory.locationid,
inventory.receiptdate, inventory.qty, inventory.lastmovedate
FROM inventory JOIN locations ON locations.siteid = inventory.siteid AND
locations.locationid = inventory.locationid
WHERE locations.siteid = :siteid_1
Any idea what I am doing wrong?
On 6/4/2010 4:37 PM, Conor wrote:
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 sqlalch...@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.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
