SQLAlchemy包含与join和contains_eager的空关系

Question

我有2个模型Recording和Recording_results像这样

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")

我需要获取其中不包含record_results并具有条件的记录项目列表（Recording.user_id ==
id，Recording.deletedd ==无，RecordingResult.deletedd ==无）

我用这个查询

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关系的录制

我使用棉花糖将结果打印到json

我的结果：

{
        "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"
    }

如何在联接查询中也包括空关系？

Answer 1

一个INNER JOIN像由

join(Recording.recording_results)

仅当联接的两侧都存在匹配行时才会产生一行。

由LEFT [OUTER] JOIN产生的AQuery.outerjoin()将在左侧包含行，而在右侧则没有匹配行，因此，如果没有其他使用右侧表的谓词，则更改联接类型会有所帮助。

实际上，您可以这样做，但是它确实是“有效的”，因为它是一种IS NULL检查。不过，ON在这种情况下，它的适当位置是子句：

outerjoin(RecordingResult, and_(Recording.recording_results, RecordingResult.deletedd_at == None))

取代的使用filter()。