Re: [sqlalchemy] Implicit join condition
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
> "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
> >. [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
Re: [sqlalchemy] Implicit join condition
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
> > . [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
> >
Re: [sqlalchemy] Implicit join condition
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
. [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)
Re: [sqlalchemy] Implicit join condition
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).
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. [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 ==
Re: [sqlalchemy] Implicit join condition
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:
[sqlalchemy] Implicit join condition
[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
