SQLAlchemy包含与join和contains_eager的空关系 [英] Sqlalchemy include empty relationship with join and contains_eager

问题描述

我有2个模型Recording and 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"
    }

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

解决方案

类似于

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

的INNER JOIN

join(Recording.recording_results)

仅当连接的两边都存在匹配行时才会产生一行.

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

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

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

替换了filter()的使用.

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

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)

I used this query

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

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

My result:

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

Expecting result:

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

解决方案

An INNER JOIN like the one produced by

join(Recording.recording_results)

will only produce a row when a matching row exists on both sides of the 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.

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

which replaces the use of filter().

这篇关于SQLAlchemy包含与join和contains_eager的空关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！