SQLAlchemy: Unnecessary join with joinedload

Question

I'm using SQLAlchemy ORM for a Flask project where I want to join across an eagerly loaded model but this leads to two joins to the same intermediary model. If you run the code below you'll see in the generated SQL that there are two joins between the Author model and the Book model. If the lazy=joined bit is removed the sql generated is perfect.

I don't know if I'm doing something wrong or this is by design. How do I get SQLAlchemy to emit the right SQL while maintaining the joinedload in this case?

Note: I have tried this with MySQL and SQLite and it happens with both those dbs.

from sqlalchemy import create_engine, Integer, String, Column
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()

from sqlalchemy import create_engine, Integer, String, Column
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
Base = declarative_base()


class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    pseudo = Column(String)
    books = relationship("Book", lazy='joined')

    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
            self.name, self.fullname, self.password)


class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    author_id = Column(Integer, ForeignKey('authors.id'))
    name = Column(String)
    user = relationship("Author", back_populates="books")
    pages = relationship("Page")


class Page(Base):
    __tablename__ = 'pages'
    id = Column(Integer, primary_key=True)
    book_id = Column(Integer, ForeignKey('books.id'))
    text = Column(String)
    book = relationship("Book", back_populates="pages")


Base.metadata.create_all(engine)
session = Session()

print(str(session.query(Author).outerjoin(Author.books, Page)))

Related: https://stackoverflow.com/questions/47243397/sqlalchemy-joinedload-filter-column, https://stackoverflow.com/questions/9821358/filtering-with-joined-tables — Ilja Everilä, Jan 27 '18 at 15:49

Ilja Everilä · Accepted Answer · 2018-01-27T15:39:05.877

It is by design – read The Zen of Joined Eager Loading:

It is critical to understand the distinction that while Query.join() is used to alter the results of a query, joinedload() goes through great lengths to not alter the results of the query, and instead hide the effects of the rendered join to only allow for related objects to be present.

There are multiple somewhat similar questions in sqlalchemy, though couldn't find one that'd fit the bill exactly.

If you manually add a join, and want to use it to eager load a relationship as well, you need contains_eager():

session.query(Author).\
    outerjoin(Author.books, Book.pages).\
    options(contains_eager(Author.books).contains_eager(Book.pages))

Note that the relationship definitions Author.books and Book.pages would seem to be missing the back_populates= argument.

SQLAlchemy: Unnecessary join with joinedload

1 Answers1