例如,我有一个Parcel
模型,它有sender
和{Subject
s。
我想从特定的寄件人那里得到包裹。我不想使用Parcel.sender.has()
,因为性能的原因,我的实际表太大了。在
来自docs:
Because has() uses a correlated subquery, its performance is not nearly as good when compared against large target tables as that of using a join.
下面是一个完整的粘贴和运行示例:
from sqlalchemy import create_engine, Column, Integer, Text, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative.api import declarative_base
from sqlalchemy.orm.util import aliased
engine = create_engine('sqlite://')
Session = sessionmaker(bind=engine)
s = Session()
Base = declarative_base()
class Subject(Base):
__tablename__ = 'subject'
id = Column(Integer, primary_key=True)
name = Column(Text)
class Parcel(Base):
__tablename__ = 'parcel'
id = Column(Integer, primary_key=True)
sender_id = Column(Integer, ForeignKey('subject.id'))
receiver_id = Column(Integer, ForeignKey('subject.id'))
sender = relationship('Subject', foreign_keys=[sender_id], uselist=False, lazy='joined')
receiver = relationship('Subject', foreign_keys=[receiver_id], uselist=False, lazy='joined')
def __repr__(self):
return '<Parcel #{id} {s} -> {r}>'.format(id=self.id, s=self.sender.name, r=self.receiver.name)
# filling database
Base.metadata.create_all(engine)
p = Parcel()
p.sender, p.receiver = Subject(name='Bob'), Subject(name='Alice')
s.add(p)
s.flush()
#
# Method #1 - using `has` method - working but slow
print(s.query(Parcel).filter(Parcel.sender.has(name='Bob')).all())
因此,我尝试按别名关系进行连接和筛选,但出现了一个错误:
^{pr2}$我发现如果我用连接条件而不是关系来指定模型,它就可以工作了。但最终的SQL查询并不是我所期望的:
print(
s.query(Parcel)\
.join(Subject, Parcel.sender_id == Subject.id)\
.filter(Subject.name == 'Bob')
)
生成下一个SQL查询:
SELECT parcel.id AS parcel_id,
parcel.sender_id AS parcel_sender_id,
parcel.receiver_id AS parcel_receiver_id,
subject_1.id AS subject_1_id,
subject_1.name AS subject_1_name,
subject_2.id AS subject_2_id,
subject_2.name AS subject_2_name
FROM parcel
JOIN subject ON parcel.sender_id = subject.id
LEFT OUTER JOIN subject AS subject_1 ON subject_1.id = parcel.sender_id
LEFT OUTER JOIN subject AS subject_2 ON subject_2.id = parcel.receiver_id
WHERE subject.name = ?
在这里您可以看到subject
表被联接了三次,而不是两次。这是因为sender
和receiver
关系都配置为加载联接。第三个连接是主题,我通过它过滤。在
我希望最终查询如下所示:
SELECT parcel.id AS parcel_id,
parcel.sender_id AS parcel_sender_id,
parcel.receiver_id AS parcel_receiver_id,
subject_1.id AS subject_1_id,
subject_1.name AS subject_1_name,
subject_2.id AS subject_2_id,
subject_2.name AS subject_2_name
FROM parcel
LEFT OUTER JOIN subject AS subject_1 ON subject_1.id = parcel.sender_id
LEFT OUTER JOIN subject AS subject_2 ON subject_2.id = parcel.receiver_id
WHERE subject_1.name = ?
我认为,通过多个引用关系进行过滤不应该如此模糊,而且有更好更清晰的方法来实现。请帮我找一下。在
您将其配置为}将始终通过join加载。
sender
和{您可以更改它并手动执行
joinedload
,当您实际需要使用一个join同时加载它们时。在如果您希望保持定义不变,您可以“帮助”SQLAlchemy并指出查询已经具有用于此比较的所有数据,并且不需要额外的联接。为此,
contains_eager
选项被使用。在修改后的查询:
它产生的SQL:
^{pr2}$相关问题 更多 >
编程相关推荐