Am 18.04.2014 16:46, schrieb Michael Bayer:
the .any() and .has() operators use a correlated EXISTS query, so no
JOIN is needed (I’d probably guess LINQ does the same, actually).
If you’re trying to say something like Job.dependencies, then
“Job.dependencies” is a relationship() in that case, if it’s
many-to-many that doesn’t really matter, the any() operator knows how to
work with many-to-many that is correctly configured.
Thanks for the example, it works. I don't know what I did different in
my first attempt.
So just to be clear: anything inside any(..) always refers to the job
inside EXISTS and not to the outer job. And if for some reason I wanted
to access the outer job, I would have to use aliases, e.g.:
j = aliased(Job)
jobs = s.query(j).\
filter(j.status == 'queued').\
filter(~j.dependencies.any(Job.status != 'done', j.name=='foo'
)).all()
Cheers
Maik
Here’s a complete example:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy import event
Base = declarative_base()
# many-to-many association table
JobDependency = Table('job_dependency', Base.metadata,
Column('jobId', Integer, ForeignKey('job.id'), primary_key=True),
Column('dependsOnJobId', Integer, ForeignKey('job.id'),
primary_key=True)
)
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
status = Column(String(20), default='queued', nullable=False)
dependencies = relationship(lambda: Job,
secondary=JobDependency,
primaryjoin=id==JobDependency.c.jobId,
secondaryjoin=id==JobDependency.c.dependsOnJobId,
backref='dependencyOf')
e = create_engine("sqlite://", echo='debug')
Base.metadata.create_all(e)
s = Session(e)
j1 = Job(status='queued', dependencies=[Job(status='done'),
Job(status='done')])
j2 = Job(status='queued', dependencies=[Job(status='inprogress')])
s.add_all([j1, j2])
jobs = s.query(Job).\
filter(Job.status == 'queued').\
filter(~Job.dependencies.any(Job.status != 'done')).all()
assert jobs == [j1]
On Apr 18, 2014, at 5:20 AM, Maik Riechert <maik.riech...@arcor.de
<mailto:maik.riech...@arcor.de>> wrote:
query(Job).filter(Job.status ==
‘queued’).filter(~Job.dependencies.any(Dependency.status !=
‘done’))
One more thing. Dependency doesn't exist as a class. Job.dependencies
is a many-to-many association. That's why you probably have to use
aliases to refer to the status of the dependency Job. I just couldn't
get it to work though. The basic model:
# many-to-many association table
JobDependency = Table('job_dependency', Base.metadata,
Column('jobId', Integer, ForeignKey('job.id'), primary_key=True),
Column('dependsOnJobId', Integer, ForeignKey('job.id'),
primary_key=True)
)
class Job(Base):
__tablename__ = 'job'
id = Column(Integer, primary_key=True)
status = Column(String(20), default='queued', nullable=False)
dependencies = relationship(lambda: Job,
secondary=JobDependency,
primaryjoin=id==JobDependency.c.jobId,
secondaryjoin=id==JobDependency.c.dependsOnJobId,
backref='dependencyOf')
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to a topic in the
Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/vLzKy5ywClk/unsubscribe.
To unsubscribe from this group and all its topics, 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 http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
