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 [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> 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 [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
