[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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
