EXISTS statements with any(), but with a join

[Kent]

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?

[Conor]

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

[Kent Bower]

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?

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Conor]

On 06/04/2010 03:56 PM, Kent Bower wrote:

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?

1. Oops, looks like exists() does not detect the subquery as a select statement and tries to create its own. Try replacing .subquery() with .statement.
2. exq is missing a WHERE clause that relates Inventory to Product.

exq=DBSession.query(Inventory).join(Location).filter(Location.siteid=='03').filter(Location.productid==Product.productid).correlate(Product).statement
DBSession.query(Product).filter(exists(exq)).all()

another alternative is to construct the subquery outside of the ORM. This at least gives you the nice SELECT 1 output:

exq = select([1], from_obj=orm.join(Inventory, Location))
exq = exq.where(Inventory.productid == Product.productid)
exq = exq.where(Location.siteid == '03')
exq = exq.correlate(Product.__table__)
DBSession.query(Product).filter(exists(exq)).all()

-Conor