Apologies if this is documented and I missed it. If B has a ForeignKey (and relationship) to A (e.g. B.a_id -> A.id), then I can write query(B.b_num).join(A) without specifying the condition, and SQLAlchemy will figure out the join automatically. [See query 0 in the code below.]
It will similarly figure out the join of B with a direct query of A.id, e.g. query(A.id).order_by(A.a_num).limit(1).subquery('first_a_id_by_num') rather than with A. [See query 1 in the code below.] However, it will not work with a more complicated query of A.id, e.g. query(func.min(A.id).label('id')).subquery('max_a_id') rather than with A. [See query 2 in the code below.] I can get the implicit join to work with such a subquery by joining with a separate A.id and using the subquery to filter this A.id, but this seems more convoluted than necessary. [See query 3 in the code below.] Yes, I realize that I can avoid this problem by providing an explicit join condition, but I'd prefer to avoid that if possible. (Also, in case it matters, my actual subquery is more complicated than the func.min(A.id) example here, as it involves windowing functions, but in the end returns a single id column with values from A.id.) Is there any way to get an implicit join like query 2 below to work without resorting to the "hack" in query 3? Is it possible to do something along the lines of query(b.b_num).join(A).replace(A, subquery_returning_one_id)? Apologies if this is documented from sqlalchemy import create_engine, func, Column, Integer, ForeignKey from sqlalchemy.orm import relationship, sessionmaker from sqlalchemy.ext.declarative import declarative_base sqlite = 'sqlite:///test.db' engine = create_engine(sqlite, echo=True) Base = declarative_base(bind=engine) class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) a_num = Column(Integer) class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) b_num = Column(Integer) a_id = Column(Integer, ForeignKey(A.id)) a = relationship(A) if __name__ == '__main__': Base.metadata.drop_all() Base.metadata.create_all() session = sessionmaker(bind=engine)() session.add(B(b_num=2, a=A(a_num=1))) session.commit() q = session.query(B.b_num) subquery_returning_one_A_id = session.query(A.id).order_by(A.a_num).limit(1).subquery('first_a_id_by_num') subquery_returning_one_id = session.query(func.min(A.id).label('id')).subquery('max_a_id') i = 0 print("\n%d" % i) try: query = q.join(A) print(query.one()) except Exception as e: print("Exception:", e) i = 1 print("\n%d" % i) try: query = q.join(subquery_returning_one_A_id) print(query.one()) except Exception as e: print("Exception:", e) i = 2 print("\n%d" % i) try: query = q.join(subquery_returning_one_id) print(query.one()) except Exception as e: print("Exception:", e) i = 3 print("\n%d" % i) try: query = q.join(session.query(A.id).filter(A.id == subquery_returning_one_id.c.id).subquery('a_id_equal_to_max_a_id')) print(query.one()) except Exception as e: print("Exception:", e) i = 4 print("\n%d" % i) try: query = q.join(session.query(A.id).select_from(subquery_returning_one_id).subquery('foo')) print(query.one()) except Exception as e: print("Exception:", e) session.close_all() Relevant output: 0 2016-07-13 01:23:41,530 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2016-07-13 01:23:41,531 INFO sqlalchemy.engine.base.Engine SELECT b.b_num AS b_b_num FROM b JOIN a ON a.id = b.a_id 2016-07-13 01:23:41,532 INFO sqlalchemy.engine.base.Engine () (2,) 1 2016-07-13 01:23:41,532 INFO sqlalchemy.engine.base.Engine SELECT b.b_num AS b_b_num FROM b JOIN (SELECT a.id AS id FROM a ORDER BY a.a_num LIMIT ? OFFSET ?) AS first_a_id_by_num ON first_a_id_by_num.id = b.a_id 2016-07-13 01:23:41,539 INFO sqlalchemy.engine.base.Engine (1, 0) (2,) 2 Exception: Could not find a FROM clause to join from. Tried joining to SELECT min(a.id) AS id FROM a, but got: Can't find any foreign key relationships between 'b' and 'max_a_id'. 3 2016-07-13 01:23:41,578 INFO sqlalchemy.engine.base.Engine SELECT b.b_num AS b_b_num FROM b JOIN (SELECT a.id AS id FROM a, (SELECT min(a.id) AS id FROM a) AS max_a_id WHERE a.id = max_a_id.id) AS a_id_equal_to_max_a_id ON a_id_equal_to_max_a_id.id = b.a_id 2016-07-13 01:23:41,581 INFO sqlalchemy.engine.base.Engine () (2,) -- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.