I have a somewhat nasty query problem, which I have abstracted and
(partially) simplified into the following test case:
----- test.py -----
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker, aliased,
relationship
from sqlalchemy import Column, Integer, ForeignKey, func,
create_engine
Session = scoped_session(sessionmaker())
Base = declarative_base()
class TableA (Base):
__tablename__ = 'table_a'
id = Column(Integer, primary_key=True)
some_key = Column(Integer, nullable=False)
class TableB (Base):
__tablename__ = 'table_b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey(TableA.id), nullable=False)
a = relationship(TableA)
class TableC (Base):
__tablename__ = 'table_c'
id = Column(Integer, primary_key=True)
b_id = Column(Integer, ForeignKey(TableB.id), nullable=False)
b = relationship(TableB)
group_key = Column(Integer, nullable=False)
sort_key = Column(Integer, nullable=False)
def make_data():
a = TableA(some_key=42)
b1 = TableB(a=a)
Session.add(TableC(b=b1, group_key=11, sort_key=0))
Session.add(TableC(b=b1, group_key=11, sort_key=1))
Session.add(TableC(b=b1, group_key=7, sort_key=0))
Session.add(TableC(b=b1, group_key=7, sort_key=1))
b2 = TableB(a=a)
Session.add(TableC(b=b2, group_key=11, sort_key=2))
def do_query():
b1 = aliased(TableB)
c1 = aliased(TableC)
q = (
Session.query(TableC.id, TableC.group_key)
.join(TableC.b, TableB.a)
.filter(TableA.some_key == 42)
.filter(TableC.sort_key ==
Session.query(func.max(c1.sort_key))
.join(b1)
.filter(c1.group_key == TableC.group_key)
.as_scalar())
)
print q.all()
e = create_engine('sqlite:///', echo=True)
Session.configure(bind=e)
Base.metadata.create_all(bind=Session.bind)
make_data()
do_query()
----- test.py -----
This returns one row: (5, 11). I expect it to return two rows, one
for each group_key -- so it should also return (4, 7). The emitted
SQL is:
SELECT table_c.id AS table_c_id, table_c.group_key AS
table_c_group_key
FROM table_c JOIN table_b ON table_b.id = table_c.b_id JOIN table_a ON
table_a.id = table_b.a_id
WHERE table_a.some_key = ? AND table_c.sort_key = (SELECT
max(table_c_1.sort_key) AS max_1
FROM table_c, table_c AS table_c_1 JOIN table_b AS table_b_1 ON
table_b_1.id = table_c_1.b_id
WHERE table_c.group_key = table_c_1.group_key)
The problem is in the FROM clause of the subquery -- there is an extra
table_c in there. If I manually run that query without the first
table_c in there, then I get the correct result. So the query should
be:
SELECT table_c.id AS table_c_id, table_c.group_key AS
table_c_group_key
FROM table_c JOIN table_b ON table_b.id = table_c.b_id JOIN table_a ON
table_a.id = table_b.a_id
WHERE table_a.some_key = ? AND table_c.sort_key = (SELECT
max(table_c_1.sort_key) AS max_1
FROM table_c AS table_c_1 JOIN table_b AS table_b_1 ON table_b_1.id =
table_c_1.b_id
WHERE table_c.group_key = table_c_1.group_key)
How do I cajole SQLAlchemy into producing this query?
--
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.
