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.
