On Aug 4, 2011, at 7:22 PM, Mark Erbaugh wrote: > Thanks, > > Could you explain how to do contains_eager with an explicit query(). I tried > putting a query inside a call to contains_eager, but get an error: > > ArgumentError: mapper option expects string key or list of attributes
So I think both approaches have advantages, the one here is nice because it will work with any query. A more efficient query doesn't rely upon the correlated subquery, and instead joins to a grouping, allowing all the max(date) rows to be found at once: subq = s.query(B.a_id, func.max(B.date).label('date')).group_by(B.a_id).subquery() for obj in s.query(A).join(A.bs).\ join(subq, A.bs).\ filter(subq.c.date==B.date).options(contains_eager(A.bs)): print obj.bs the SQL here is: SELECT b.id AS b_id, b.a_id AS b_a_id, b.date AS b_date, a.id AS a_id FROM a JOIN b ON a.id = b.a_id JOIN (SELECT b.a_id AS a_id, max(b.date) AS date FROM b GROUP BY b.a_id) AS anon_1 ON a.id = anon_1.a_id WHERE anon_1.date = b.date let's see mongodb do that ! :) I'm keeping a running track of these examples at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/RelationshipToLatest as this use is something that has come up a lot before. > > Mark > > On Aug 4, 2011, at 6:39 PM, Michael Bayer wrote: > >> awkardly and inefficiently from a SQL perspective. contains_eager() with >> an explicit query() would produce better result >> >> >> from sqlalchemy import * >> from sqlalchemy.orm import * >> from sqlalchemy.ext.declarative import declarative_base >> Base = declarative_base() >> import datetime >> >> class A(Base): >> __tablename__ = 'a' >> id = Column(Integer, primary_key=True) >> bs = relationship("B") >> >> class B(Base): >> __tablename__ = 'b' >> id = Column(Integer, primary_key=True) >> a_id = Column(Integer, ForeignKey('a.id')) >> date = Column(Date) >> >> A.latest_b = relationship(B, >> primaryjoin=and_( >> A.id==B.a_id, >> >> B.date==select([func.max(B.date)]).where(B.a_id==A.id).correlate(A.__table__) >> ) >> ) >> >> e = create_engine('sqlite://', echo=True) >> Base.metadata.create_all(e) >> s = Session(e) >> >> s.add_all([ >> A(bs=[ >> B(date=datetime.date(2011, 10, 5)), >> B(date=datetime.date(2011, 8, 4)), >> B(date=datetime.date(2011, 9, 17)), >> ]), >> A(bs=[ >> B(date=datetime.date(2011, 10, 5)), >> B(date=datetime.date(2011, 8, 4)), >> B(date=datetime.date(2011, 9, 17)), >> ]), >> ]) >> s.commit() >> >> for obj in s.query(A).options(joinedload(A.latest_b)): >> print obj.latest_b >> >> >> >> On Aug 4, 2011, at 5:55 PM, Mark Erbaugh wrote: >> >>> Table A has a one to many relationship with Table B. There may be zero or >>> more rows in B for each row in A. >>> >>> I would like to have a query that retrieves all the rows in table A joined >>> with the "first" related row in table B (if one exists). In this case, each >>> row in table B has a DATE field and I want to retrieve the row with the >>> latest date. Is this possible using joinedload? >>> >>> Thanks, >>> Mark >>> >>> -- >>> 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. >> > > -- > 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.