I am trying to perform a query in which I from multiple correlated
subqueries. My code works with 0.7.10, but not with 0.9.8.
Base = declarative_base()
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
name = Column(String)
class Action(Base):
__tablename__ = 'action'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey(User.id))
action = Column(String)
date = Column(DateTime)
engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(bind=engine)
sess = sessionmaker(bind=engine)()
sess.add(User(id=1, name='Meelap'))
sess.flush()
sess.add(Action(id=1, user_id=1, action="login", date=datetime.now()))
sess.flush()
num_actions = sess.query(func.count()).filter(Action.user_id == User.id).
correlate(User).as_scalar()
first_action = sess.query(func.min(Action.date)).filter(Action.user_id ==
User.id).correlate(User).as_scalar()
q1 = sess.query(User.name, num_actions, first_action)
print sqlalchemy.__version__
print
print str(q1)
print
print q1.all()
With 0.7.10, it works as I expect with the User table being dropped from
the FROMs of the subqueries.
0.7.10
SELECT "user".name AS user_name, (SELECT count(*) AS count_1
FROM action
WHERE action.user_id = "user".id) AS anon_1, (SELECT min(action.date) AS
min_1
FROM action
WHERE action.user_id = "user".id) AS anon_2
FROM "user"
[(u'Meelap', 1, datetime.datetime(2014, 11, 4, 19, 37, 15, 260873))]
With 0.9.8, the str(query) is the same with User having been dropped from
subqueries, but the error output shows a different subquery being executed
that selects from both Action and User.
0.9.8
SELECT "user".name AS user_name, (SELECT count(*) AS count_1
FROM action
WHERE action.user_id = "user".id) AS anon_1, (SELECT min(action.date) AS
min_1
FROM action
WHERE action.user_id = "user".id) AS anon_2
FROM "user"
Traceback (most recent call last):
File "sql.py", line 39, in <module>
print q1.all()
File
"/home/ubuntu/.virtualenvs/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py"
, line 2320, in all
return list(self)
File
"/home/ubuntu/.virtualenvs/env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py"
, line 76, in instances
labels) for row in fetch]
File
"/home/ubuntu/.virtualenvs/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py"
, line 3523, in proc
return row[column]
File
"/home/ubuntu/.virtualenvs/env/lib/python2.7/site-packages/sqlalchemy/engine/result.py"
, line 331, in _key_fallback
expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: 'Could not locate column in row for
column \'(SELECT count(*) AS count_1 \nFROM action, "user" \nWHERE
action.user_id = "user".id)\''
I searched the changelogs but haven't found anything that could explain
this and I'd appreciate any help. Thanks!
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
