On 07/13/2016 02:22 PM, Seth P wrote:
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.)
OK, here is the most fancy-pants solution there is, show it to a
potential employer and you'll get any sqlalchemy job anywhere :
from sqlalchemy.sql import join, visitors
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']
onclause = join(joining_to, A).onclause
def replace(obj):
if A.id.shares_lineage(obj):
return subquery_returning_one_id.c.id
onclause = visitors.replacement_traverse(onclause, {}, replace)
q = q.join(subquery_returning_one_id, onclause)
return q
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
<http://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
<http://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>
> <http://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>
<http://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>
> <http://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>
<http://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>
> <http://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> <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> <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>
> <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> <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> <http://a.id>) AS id
> > FROM a) AS max_a_id ON b.a_id = max_a_id.id
<http://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> <http://a.id> AS id
> > FROM a, (SELECT min(a.id <http://a.id> <http://a.id>) AS id
> > FROM a) AS max_a_id
> > WHERE a.id <http://a.id> <http://a.id> = max_a_id.id
<http://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>
<http://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> <http://a.id> AS id
> > FROM a, (SELECT min(a.id <http://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>
<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>
> <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>
> <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
<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+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.
