Hi Simon - yeah that looks pretty buglike to me, mapper.order_by is not a frequently used feature so this one may need some adjustment.
I've created http://www.sqlalchemy.org/trac/ticket/2287 to take a look at this and so far I'm targeting it at 0.6.9/0.7.3. On Sep 27, 2011, at 7:29 AM, King Simon-NFHD78 wrote: > 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. > -- 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.
