Sqlalchemy 包含与 join 和 contains_eager 的空关系 [英] Sqlalchemy include empty relationship with join and contains_eager
问题描述
我有 2 个模型 Recording 和 Recording_results 像这样
I have 2 models Recording and Recording_results like so
class Recording(Base):
__tablename__ = 'recordings'
id = Column(Integer, primary_key=True)
filename = Column(String, nullable=False)
language_id = Column(Integer, ForeignKey('languages.id'), nullable=False)
language = relationship("Language", back_populates="recordings")
user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
user = relationship("User", back_populates="recordings")
created_at = Column(DateTime, nullable=False, default=func.now())
updated_at = Column(DateTime, nullable=False,
default=func.now(), onupdate=func.now())
deletedd_at = Column(DateTime, nullable=True)
def __repr__(self):
return 'id: {}'.format(self.id)
User.recordings = relationship("Recording", order_by=Recording.id, back_populates="user")
Language.recordings = relationship("Recording", order_by=Recording.id, back_populates="language")
class RecordingResult(Base):
__tablename__ = 'recording_results'
id = Column(Integer, primary_key=True)
is_with_dictionary = Column(Boolean, default=False)
result = Column(String, nullable=True)
run_time = Column(Float, default=0.0)
recording_id = Column(Integer, ForeignKey('recordings.id'), nullable=False)
recording = relationship("Recording", back_populates="recording_results", lazy="joined")
speech_service_id = Column(Integer, ForeignKey('speech_services.id'), nullable=False)
speech_service = relationship("SpeechService", back_populates="recording_results")
created_at = Column(DateTime, nullable=False, default=func.now())
updated_at = Column(DateTime, nullable=False,
default=func.now(), onupdate=func.now())
deletedd_at = Column(DateTime, nullable=True)
def __repr__(self):
return 'id: {}'.format(self.id)
Recording.recording_results = relationship("RecordingResult", order_by="desc(RecordingResult.id)", back_populates="recording", lazy="joined")
SpeechService.recording_results = relationship("RecordingResult", order_by=RecordingResult.id, back_populates="speech_service")
我需要获取不包括recording_results的记录项目列表和条件(Recording.user_id == id, Recording.deletedd==None, RecordingResult.deletedd==None)
I need to get list of items of recording that uncluding recording_results in it and with conditions(Recording.user_id == id, Recording.deletedd==None, RecordingResult.deletedd==None)
我用过这个查询
db.session.query(Recording).filter(Recording.user_id == id, Recording.deletedd_at == None).order_by(Recording.id.desc()).join(Recording.recording_results).options(contains_eager(Recording.recording_results)).filter(RecordingResult.deletedd_at == None).all()
它过滤掉但似乎join方法不包括带有空recording_results关系的记录
And it filter out but seems like the join method does not include recording with empty recording_results relationship
我使用棉花糖打印出的结果
The result i print out to json using marshmallow
我的结果:
{
"id": 4,
"filename": "15615378415768423_test.txt",
"language_id": 1,
"user_id": 2,
"recording_results": [
{
"id": 5,
"is_with_dictionary": true,
"result": "test5",
"run_time": 1200.2,
"recording_id": 4,
"speech_service_id": 2
}
],
"created_at": "2019-06-26T08:30:41.591410+00:00"
},
{
"id": 2,
"filename": "15615371606083994_test.txt",
"language_id": 2,
"user_id": 2,
"recording_results": [
{
"id": 1,
"is_with_dictionary": true,
"result": "test1",
"run_time": 1500.2,
"recording_id": 2,
"speech_service_id": 4
},
{
"id": 2,
"is_with_dictionary": false,
"result": "test2",
"run_time": 1600.2,
"recording_id": 2,
"speech_service_id": 3
}
],
"created_at": "2019-06-26T08:19:20.628205+00:00"
}
预期结果:
{
"id": 5,
"filename": "15616009750201173_test.txt",
"language_id": 1,
"user_id": 2,
"recording_results": [],
"created_at": "2019-06-27T02:02:55.035810+00:00"
},
{
"id": 4,
"filename": "15615378415768423_test.txt",
"language_id": 1,
"user_id": 2,
"recording_results": [
{
"id": 5,
"is_with_dictionary": true,
"result": "test5",
"run_time": 1200.2,
"recording_id": 4,
"speech_service_id": 2
}
],
"created_at": "2019-06-26T08:30:41.591410+00:00"
},
{
"id": 2,
"filename": "15615371606083994_test.txt",
"language_id": 2,
"user_id": 2,
"recording_results": [
{
"id": 2,
"is_with_dictionary": false,
"result": "test2",
"run_time": 1600.2,
"recording_id": 2,
"speech_service_id": 3
},
{
"id": 1,
"is_with_dictionary": true,
"result": "test1",
"run_time": 1500.2,
"recording_id": 2,
"speech_service_id": 4
}
],
"created_at": "2019-06-26T08:19:20.628205+00:00"
}
如何在连接查询中也包含空关系?
How do i include empty relationship in the join query too ?
推荐答案
一个 INNER JOIN 类似于
join(Recording.recording_results)
仅当连接的两侧都存在匹配的行时才会生成一行.
will only produce a row when a matching row exists on both sides of the join.
由 Query.outerjoin() 生成的 LEFT [OUTER] JOIN 将包括左侧没有右侧匹配行的行，因此更改联接如果您没有使用正确表的其他谓词，则类型会有所帮助.
A LEFT [OUTER] JOIN, produced by Query.outerjoin() will include rows on the left that have no matching row on the right, so changing the join type helps, if you have no additional predicates that use the right table.
事实上你这样做了，但它按原样工作"，因为它是一个IS NULL检查.不过，在这种情况下，它的正确位置是 ON 子句:
In fact you do, but it sort of "works" as is, because it is an IS NULL check. Still, its proper place is the ON clause in this case:
outerjoin(RecordingResult, and_(Recording.recording_results, RecordingResult.deletedd_at == None))
取代了filter()的使用.
这篇关于Sqlalchemy 包含与 join 和 contains_eager 的空关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
