This example is simplified from my actual code, in which I am filtering the child objects, so joinedload isn't an option.
I've solved the issue by using .one() instead of .first(), which seems to eliminate the filter, so everything is working, although I'm not sure what is being done differently by the 2 calls. On Tuesday, May 5, 2015 at 3:32:10 AM UTC-7, Simon King wrote: > > On Tue, May 5, 2015 at 2:45 AM, Gerald Thibault <diesel...@gmail.com > <javascript:>> wrote: > > I have 2 classes, User and Address (i tried to make my test case > resemble > > the contents of > > > http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#contains-eager), > > > > defined like this: > > > > > > from sqlalchemy import * > > from sqlalchemy.ext.declarative import declarative_base > > from sqlalchemy.orm import * > > > > > > e = create_engine('sqlite:////tmp/test.db', echo=True) > > Base = declarative_base() > > Base.metadata = MetaData(e) > > > > class User(Base): > > __tablename__ = 'user' > > id = Column(Integer, primary_key=True) > > addresses = relationship("Address") > > > > class Address(Base): > > __tablename__ = 'address' > > id = Column(Integer, primary_key=True) > > user_id = Column(Integer, ForeignKey(User.id)) > > > > I am populating the db with this: > > > > if __name__ == '__main__': > > Base.metadata.drop_all() > > Base.metadata.create_all() > > > > address1 = Address() > > address2 = Address() > > address3 = Address() > > user = User(id=1, addresses=[address1, address2]) > > > > session = Session(e) > > session.add(user, address3) > > session.commit() > > > > > > I can use this query to get expected results: > > > > q = session.query(User) \ > > .filter(User.id==1) \ > > .first() > > print q.addresses > > # [<__main__.Address object at 0x7f93bf5e46d0>, <__main__.Address > object > > at 0x7f93bf5e4710>] > > > > The sql for this is (in sqlite, although the issue occurs in mysql as > well, > > but with a slightly different query): > > > > SELECT user.id AS user_id > > FROM user > > WHERE user.id = 1 > > LIMIT 1 OFFSET 0 > > > > > > this attempt, however, includes a 'limit 1' which is restricting the > > addresses to 1: > > > > q = session.query(User) \ > > .outerjoin(User.addresses) \ > > .options(contains_eager(User.addresses)) \ > > .filter(User.id==1) \ > > .first() > > print q.addresses > > # [<__main__.Address object at 0x7f93bf5a3fd0>] > > > > the sql: > > > > SELECT address.id AS address_id, address.user_id AS address_user_id, > user.id > > AS user_id > > FROM user LEFT OUTER JOIN address ON user.id = address.user_id > > WHERE user.id = 1 > > LIMIT 1 OFFSET 0 > > > > to make it work, I am currently doing this: > > q = session.query(User) \ > > .outerjoin(User.addresses) \ > > .options(contains_eager(User.addresses)) \ > > .filter(User.id==1) \ > > .all() > > if q: > > q = q[0] > > > > > > This works, but seems kind of dirty. Is this the best way for me to do > this? > > What do I need to change in order to have contains_eager populate all > > related objects instead of just the first one? > > > > It sounds like you want joinedload(User.addresses), rather than > contains_eager. contains_eager means "I've already joined to the > addresses table and selected the rows I want - please put them > straight in the addresses property", whereas joinedload means "please > do whatever is necessary to the query to join to the addresses table > and load *all* the related rows". If you've applied a limit to the > query, SA turns your original query into a subquery with the limit > applied, and then wraps it in an outer query that joins to the > addresses table to get all the rows you need. > > This also means that you don't need "outerjoin(User.addresses)" - SA > will do that automatically. You only need to join explicitly if you > want to filter by that attribute (eg. if you only wanted to retrieve > users that lived in a certain city). > > > http://docs.sqlalchemy.org/en/improve_toc/orm/loading_relationships.html#the-zen-of-eager-loading > > > Hope that helps, > > Simon > -- 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.
