you'd have to construct the joinedload() as a join(), create the subquery
manually, and use contains_eager() to establish the joined rows as a collection
load. With some relational databases, if the combination of the inner
subquery and the joined rows aren't subject to an ORDER BY, the ordering of the
results is non deterministic, so in the general case this ORDER BY is necessary.
On May 6, 2014, at 3:43 PM, Gerald Thibault <dieselmach...@gmail.com> wrote:
> 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.
--
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.
