I am trying to query for a model joined to another model via a one-to-many
relationship. When i try to order this, i end up with an ordered subquery,
but the results from the subquery are then ordered again, which results in
a filesort (without the 2nd order by, everything is handled cleanly with
indexes). How can i eliminate the second ORDER BY from this?
Source:
import sys
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, relationship, backref, joinedload
e = create_engine('sqlite:////tmp/test.db', echo=True)
Base = declarative_base()
Base.metadata = MetaData(e)
class Item(Base):
__tablename__ = 'items'
id = Column(Integer, primary_key=True)
class SubItem(Base):
__tablename__ = 'subitems'
id = Column(Integer, primary_key=True)
item_id = Column(Integer, ForeignKey(Item.id))
item = relationship(Item, backref='subitems')
if __name__ == '__main__':
Base.metadata.drop_all()
Base.metadata.create_all()
session = Session(e)
sites = session.query(Item) \
.options(joinedload('subitems')) \
.order_by(Item.id) \
.limit(20) \
.offset(20) \
.all()
generated query:
SELECT anon_1.items_id AS anon_1_items_id, subitems_1.id AS subitems_1_id,
subitems_1.item_id AS subitems_1_item_id
FROM (
SELECT items.id AS items_id
FROM items
ORDER BY items.id
LIMIT ?
OFFSET ?) AS anon_1
LEFT OUTER JOIN subitems AS subitems_1
ON anon_1.items_id = subitems_1.item_id
ORDER BY anon_1.items_id
--
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.
