On Apr 25, 2013, at 4:31 PM, Joril <jor...@gmail.com> wrote:
> Hi everyone!
> I have this working query:
>
> select *
> from A join B on A.id = B.a_id
> where exists (select 1 from C where A.id = C.a_id and C.value > B.value)
>
> and I tried to implement it like this:
>
> q = Session.query(entities.A)
> q = q.join((entities.B, entities.A.id == entities.B.a_id))
> q = q.filter(entities.A.list_of_Cs.any(entities.C.value > entities.B.value)
>
> but the generated SQL looks like:
>
> SELECT [...fields...]
> FROM a JOIN b ON a.id = b.a_id
> WHERE EXISTS (SELECT 1
> FROM C, B
> WHERE A.id = C.a_id AND C.value > B.value)
>
> As you can see, the inner select doesn't get B.value from the main query as
> in my original one, instead it introduces a new join between C and B...
> I tried replacing entities.B like this:
> b_alias = entities.B.__table__.alias()
> but I still get a join inside the subquery... What am I missing?
this will work out of the box in 0.8 as auto-correlation has been improved a
lot. in 0.7 you can add correlate() explicitly:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
cs = relationship("C")
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
value = Column(Integer)
class C(Base):
__tablename__ = 'c'
id = Column(Integer, primary_key=True)
a_id = Column(Integer, ForeignKey('a.id'))
value = Column(Integer)
e = create_engine("sqlite://", echo=True)
sess = Session(e)
# in 0.8
q = sess.query(A).join(B, A.id == B.a_id).filter(
A.cs.any(C.value > B.value)
)
print q
# in 0.7 or 0.8
q = sess.query(A).join(B, A.id == B.a_id).filter(
A.cs.any(C.value > B.value).correlate(B.__table__)
)
print q
>
> Many thanks!
> (SQLAlchemy 0.7.9)
>
> --
> 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 http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
