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?
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
