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.
