On 07/13/2016 02:29 AM, 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 ofA.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.)
So, when you do a thing like query(B).join(A), it is using the foreign
keys between B and A to figure that out, but *not* the relationship
"B.a". If OTOH you do query(B).join(B.a), then you *are* using the
relationship.
With the join to the subquery that's on func.max(A.id), once you use
that function, the column loses it's "A.id-ness", because SQLA doesn't
know anything about func.max() and for all it knows it could be turning
it into anything. So neither a join on FKs nor on the existing
relationship can figure that out immediately.
It depends here on where you are OK doing the explicit mentioning of
A.id and B.a_id. it has to be somewhere. It can be:
1. in a new relationship() that you put on A or B, that doesn't normally
load but you can use it here
2. when you make the subquery, include B.a_id in it somehow, like either
select from B.a_id instead of A.id (if that applies), or pre-fabricate
your join condition:
q = session.query(B.b_num)
subquery_returning_one_id =
session.query(func.min(A.id).label('id')).subquery('max_a_id')
j = subquery_returning_one_id.join(B, B.a_id ==
subquery_returning_one_id.c.id)
query = q.select_from(j)
3. build yourself a function, if you want it to look nice you can use
with_transformation()
def join_to_min_a(q):
subquery_returning_one_id =
session.query(func.min(A.id).label('id')).subquery('max_a_id')
q = q.join(subquery_returning_one_id,
subquery_returning_one_id.c.id == B.a_id)
return q
q = session.query(B.b_num)
q = q.with_transformation(join_to_min_a)
print(q.one())
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
