On Mar 26, 2014, at 1:56 PM, Staszek <stf.list.ot...@eisenbits.com> wrote:
> On 2014-03-26 18:16, Michael Bayer wrote: >> It's not totally clear which of the common use cases you have here, pick one: >> >> 1. I want to emit a JOIN from User to Car and order by Car.model - don't use >> eager loading, use query.join(): > > This one. > >> http://docs.sqlalchemy.org/en/rel_0_9/faq.html#i-m-using-joinedload-or-lazy-false-to-create-a-join-outer-join-and-sqlalchemy-is-not-constructing-the-correct-query-when-i-try-to-add-a-where-order-by-limit-etc-which-relies-upon-the-outer-join > > Ok but this will result in duplicate joins, right? It does not look too > efficient, does it? > > It was discussed here: > http://stackoverflow.com/questions/22490777/sqlalchemy-how-to-order-query-results-order-by-on-a-relationships-field-re > . I spotted duplicate joins, which I wanted to avoid, and was advised to > use options(contains_eager(...)). If you have a lazy=False (or 'joined') on User.cars, that means, "eager load this relationship every time". However contains_eager() will supersede that joined loader as you are specifying a specific form of load for the User.cars relationship. given this mapping: from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) bs = relationship("B", backref=backref('a', lazy='joined')) class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) a_id = Column(Integer, ForeignKey('a.id')) sess.query(B).all() produces: SELECT b.id AS b_id, b.a_id AS b_a_id, a_1.id AS a_1_id FROM b LEFT OUTER JOIN a AS a_1 ON a_1.id = b.a_id sess.query(B).join(B.a).order_by(A.id).options(contains_eager(B.a)).all() produces: SELECT b.id AS b_id, b.a_id AS b_a_id, a.id AS a_id FROM b JOIN a ON a.id = b.a_id ORDER BY a.id if you are seeing something different then we need to look more specifically at what you're doing. -- 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.
