Hi,
I think there may be a bug in the interaction between 'subqueryload' and
having a default 'order_by' defined on a mapped class. When the subquery
is run, it looks like the ORDER BY is being placed on the outer query,
whereas it should be on the inner query. The full test case is below,
but here are the 2 queries (produced using hg revision 62e97372a028):
Main query
----------
SELECT master.id AS master_id, master.dummy AS master_dummy
FROM master
ORDER BY master.id DESC
LIMIT 2 OFFSET 0
Subquery
--------
SELECT detail.id AS detail_id, detail.master_id AS detail_master_id,
anon_1.master_id AS anon_1_master_id
FROM (SELECT master.id AS master_id
FROM master
LIMIT 2 OFFSET 0) AS anon_1
JOIN detail ON anon_1.master_id = detail.master_id
ORDER BY anon_1.master_id
Since the ORDER BY is not on the inner query, a different set of
'master' rows is referenced than in the main query.
Cheers,
Simon
--------------------------------------------------------------------
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Master(Base):
__tablename__ = 'master'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
dummy = sa.Column(sa.Integer)
__mapper_args__ = {'order_by': sa.desc(id)}
class Detail(Base):
__tablename__ = 'detail'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
master_id = sa.Column(sa.Integer, sa.ForeignKey(Master.id))
master = saorm.relationship(Master, backref='details')
def test():
dburi = 'sqlite://'
engine = sa.create_engine(dburi, echo=True)
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)
session = saorm.create_session(bind=engine)
session.begin()
# Insert 5 masters, each with 1 detail
for i in range(5):
master = Master(dummy=i)
master.details.append(Detail())
session.add(master)
session.commit()
session.close()
# Load back 2 masters, using subqueryload to load the detail
# rows. If you uncomment the '.order_by' line here, the test
# passes.
master_query = (session.query(Master)
#.order_by(sa.desc(Master.id))
.limit(2)
.options(saorm.subqueryload('details')))
# Display the details for each master
for item in master_query:
print 'Master %s: %s' % (item.id, item.details),
if len(item.details) == 0:
print 'FAIL'
else:
print 'PASS'
if __name__ == '__main__':
test()
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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.
