[Apologies for posting an incomplete version of this post earlier. Please ignore 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'). [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'). [See query 2 in the code below.] Of course, I can make it work by specifying the join condition B.a_id == subquery_returning_one_id.c.id. [See query 3 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 4 in the code below.] I can also get it to work with query(A.id).select_from(subquery_returning_one_id).subquery( 'a_id_from_max_a_id'), but like query 4, this also introduces an extra reference to A.id. [See query 5 in the code below.] Is there any way to get an implicit join like query 2 to produce sql as in query 3, without introducing (explicitly as in query 4 or implicitly as in query 5) an extra reference to A.id? Or is the extra copy of A.id in queries 4 and 5 pretty harmless performance-wise, and I should just deal with it as the cost of not providing an explicit join condition? 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, but in the end returns a single column labeled id with values from A.id.) 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(subquery_returning_one_id, B.a_id == subquery_returning_one_id.c.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).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 = 5 print("\n%d" % i) try: query = q.join(session.query(A.id).select_from(subquery_returning_one_id).subquery('a_id_from_max_a_id')) print(query.one()) except Exception as e: print("Exception:", e) session.close_all() Relevant output: 0 2016-07-13 02:17:41,901 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2016-07-13 02:17:41,902 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 02:17:41,902 INFO sqlalchemy.engine.base.Engine () (2,) 1 2016-07-13 02:17:41,902 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 02:17:41,908 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 02:17:41,912 INFO sqlalchemy.engine.base.Engine SELECT b.b_num AS b_b_num FROM b JOIN (SELECT min(a.id) AS id FROM a) AS max_a_id ON b.a_id = max_a_id.id 2016-07-13 02:17:41,914 INFO sqlalchemy.engine.base.Engine () (2,) 4 2016-07-13 02:17:41,917 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 02:17:41,920 INFO sqlalchemy.engine.base.Engine () (2,) 5 2016-07-13 02:17:41,922 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) AS a_id_from_max_a_id ON a_id_from_max_a_id.id = b.a_id 2016-07-13 02:17:41,922 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.