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.
