<pre class="lang-py prettyprint-override"><code>exists1 = db_session.query(
ParticipatingIcT.id
).filter(
(ParticipatingIcT.id == agreement.participating_ic_id),
(ParticipatingIcT.ic_nihsac == func.substr(approver.nihsac, 1, 3))
).exists() # use exists here instead of subquery
exists2 = db_session.query(
ExternalPeopleT.id
).filter(
(ExternalPeopleT.participating_ic_id == agreement.participating_ic_id),
(ExternalPeopleT.uniqueidentifier == approver.uniqueidentifier)
).exists() # use exists again instead of subquery
subqMain = db_session.query(and_(
or_(exists1, exists2),
approver.current_flag == 'Y',
approver.inactive_date == None,
approver.organizationalstat == 'EMPLOYEE')).subquery()
# ...
# Now we will select from subqMain.
agreements = (db_session.query(
OtherModel,
subqMain))
</code></pre>
<p><a href="https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.exists" rel="nofollow noreferrer">sqlalchemy.orm.Query.exists</a></p>
<h2>半生不熟的例子</h2>
<p>我在这里也使用别名,因为我不太了解将子查询包装在括号中的操作方法。如果没有它,它似乎可以工作,但是原始sql很难读取。这是我制作的一个玩具示例,用于尝试查看生成的SQL是否无效</p>
<pre><code>class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String, nullable=False)
hobbies = relationship('Hobby', backref='user')
class Hobby(Base):
__tablename__ = 'hobbies'
id = Column(Integer, primary_key=True, autoincrement=True)
user_id = Column(Integer, ForeignKey('users.id'), nullable = False)
name = Column(String, nullable=False)
Base.metadata.create_all(engine, checkfirst=True)
with Session(engine) as session:
user1 = User(name='user1')
session.add(user1)
session.add_all([
Hobby(name='biking', user=user1),
Hobby(name='running', user=user1),
Hobby(name='eating', user=user1),
])
user2 = User(name='user2')
session.add(user2)
session.add(Hobby(name='biking', user=user2))
session.commit()
nested_user = aliased(User)
subq1 = session.query(Hobby.id).filter(nested_user.id ==Hobby.user_id, Hobby.name.like('bik%'))
subq2 = session.query(Hobby.id).filter(nested_user.id ==Hobby.user_id, Hobby.name.like('eat%'))
subqmain = session.query(nested_user).filter(or_(subq1.exists(), subq2.exists()), nested_user.id > 0).subquery()
q = session.query(User).select_from(User).join(subqmain, User.id == subqmain.c.id)
print (q)
print ([user.name for user in q.all()])
</code></pre>
<pre><code>SELECT users.id AS users_id, users.name AS users_name
FROM users JOIN (SELECT users_1.id AS id, users_1.name AS name
FROM users AS users_1
WHERE ((EXISTS (SELECT 1
FROM hobbies
WHERE users_1.id = hobbies.user_id AND hobbies.name LIKE %(name_1)s)) OR (EXISTS (SELECT 1
FROM hobbies
WHERE users_1.id = hobbies.user_id AND hobbies.name LIKE %(name_2)s))) AND users_1.id > %(id_1)s) AS anon_1 ON users.id = anon_1.id
['user1', 'user2']
</code></pre>