That works! Obviously I need to know that the joining field is called a_id, and I can live with that, since in practice it's uniform. But I'm just curious if there's an automated way to figure out which entity/column is related to A. (There could be more than one entity in q, though just one that has a (unique) ForeignKey to A.)
On Wednesday, July 13, 2016 at 2:06:55 PM UTC-4, Mike Bayer wrote: > > > > On 07/13/2016 01:04 PM, Seth P wrote: > > Thank you, as always, for the quick and detailed response. > > > > 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. > > > > > > I figured as much, and obviously this is the correct behavior. I was > > hoping there was some way I could tell SQLAlchemy that subquery.id > > "possesses A.id-ness" (e.g. via .select_from()), but that doesn't appear > > to be the case. > > > > 3. build yourself a function, if you want it to look nice you can > use > > with_transformation() > > > > > > This is exactly what I'm doing (minus the with_transformation(), which > > I'm about to look up...). The problem is that there are additional > > classes C, D, E, F, etc. that all point to A (via a ForeignKey and a > > relationship), and the query q could involve any one of them. (Obviously > > if it involved more than one, I would need to specify the join > > explicitly.) So it's not at all straightforward (at least to me) to > > figure out on what to join -- unless I require that it be explicitly > > provided as an argument to the function, i.e. join_to_min_a(q, > > field_to_join_to_A_id). > > OK then how about this: > > def join_to_min_a(q): > subquery_returning_one_id = > session.query(func.min(A.id).label('id')).subquery('max_a_id') > joining_to = q.column_descriptions[0]['entity'].a_id > q = q.join(subquery_returning_one_id, > subquery_returning_one_id.c.id == joining_to) > return q > > > > > > > > > > > > On Wednesday, July 13, 2016 at 12:16:52 PM UTC-4, Mike Bayer wrote: > > > > > > > > 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 > > <http://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 < > http://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 > > <http://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 < > http://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 > > > > <http://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 <http://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 <http://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 > > <http://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 <http://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 <http://a.id>) AS id > > > FROM a) AS max_a_id ON b.a_id = max_a_id.id <http://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 <http://a.id> AS id > > > FROM a, (SELECT min(a.id <http://a.id>) AS id > > > FROM a) AS max_a_id > > > WHERE a.id <http://a.id> = max_a_id.id <http://max_a_id.id>) AS > > a_id_equal_to_max_a_id ON > > > a_id_equal_to_max_a_id.id <http://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 <http://a.id> AS id > > > FROM a, (SELECT min(a.id <http://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 <http://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+...@googlegroups.com <javascript:> > > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:> > <javascript:>>. > > > To post to this group, send email to sqlal...@googlegroups.com > > <javascript:> > > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > > Visit this group at https://groups.google.com/group/sqlalchemy > > <https://groups.google.com/group/sqlalchemy>. > > > For more options, visit https://groups.google.com/d/optout > > <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+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > 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.