One-to-many relationships with lazy="subquery" or lazy="joined" give "SAWarning: Multiple rows returned..."

[Former user]

We have some code with a one-to-many relationship that we want to normally populate in an eager manner. The relationship definition had the attribute lazy="subquery" to achieve this.

If a query is then written that attempts to explicitly load the one-to-many collection and its backref eagerly, then you can trigger the warning: SAWarning: Multiple rows returned with uselist=False for eagerly-loaded attribute '...'. Clearly there is redundant complexity here, the query should not need to duplicate the eager loading behaviour specified by the lazy="subquery" attribute. But I don't think that it should cause a problem either: more complex queries that want to join against additional tables need to introduce this complexity.

We found the behaviour differs between SQLAlchemy 0.6 and 0.7 (0.7 generates the warning in a greater variety of situations). This has caused problems trying to run some of our code under SQLAlchemy 0.7, while maintaining compatibility with SQLAlchemy 0.6. We have found a solution, but 3/4s of the combinations of query and lazy attribute we tried trigger the warning on one version of SQLAlchemy or the other. This is probably easiest explained by the attached code, but I'll try and explain it here too.

With the relationship specifying lazy="subquery" we found that SQLAlchemy 0.6 could correctly run queries that used the 'eagerload' option in the query, while 0.7 would generate the warning. Rewriting the query to use an outerjoin makes the code work without a warning in SQLAlchemy 0.7 but generates the warning in SQLAlchemy 0.6. Switching the relationship from lazy="subquery" to lazy="joined" allowed the outerjoin style query to work in both versions of SQLAlchey, while the eagerload form of the query generates a warning in SQLAlchemy 0.7.

[Former user]

Demonstration .py file

[Mike Bayer]

There's no issue here as the usage of contains_eager() in both examples is incorrect. The warning regarding multiple rows always indicates a misconfiguration of some kind. The difference in behavior between 0.6 and 0.7 here is due to the enhancement of #2032 mentioned at http://www.sqlalchemy.org/trac/wiki/07Migration#contains_eagerchainsacrossmultiplepathsi.e.all causing the misconfiguration to have a different end result.

Each clause passed to contains_eager() must be represented as a JOIN placed explicitly in the query. This JOIN cannot be the one generated by the joinedload() (also called eagerload()) directive. Examples and documentation for this is at http://www.sqlalchemy.org/docs/orm/loading.html#routing-explicit-joins-statements-into-eagerly-loaded-collections . Detail on the behavior of joinedload() regarding the mis-use of it for ad-hoc joins is described there as well at http://www.sqlalchemy.org/docs/orm/loading.html#the-zen-of-eager-loading.

In this case, a full contains_eager() + explicit joins would need to use aliasing:

proj_alias = aliased(Project)
session.query(Project).\
    outerjoin(Project.configs).\
    outerjoin((proj_alias, Config.project)).\
    options(contains_eager(Project.configs)).\
    options(contains_eager(Project.configs, Config.project, alias=proj_alias)).all()

However, this verbosity is unnecessary. Config.project will guaranteed be present without any SQL emitted as it is a many-to-one complementing the one-to-many collection which is being loaded. You'll get a faster query and no additional SQL just dealing with Project.configs:

for p in session.query(Project).\
    outerjoin(Project.configs).\
    options(contains_eager(Project.configs)):
    for c in p.configs:
        print c.project

The usage of contains_eager() with a join() is mutually exclusive of usage with joinedload. In this case joinedload or subqueryload can be used with less verbosity:

for p in session.query(Project).\
    options(joinedload(Project.configs)):
    for c in p.configs:
        print c.project

hope this helps !