Actually, taking a closer look, the sql generated for query 5 doesn't look correct (or at least not what I want), since it isn't joining max_a_id with anything.
On Wednesday, July 13, 2016 at 2:29:34 AM UTC-4, Seth P wrote: > > [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.