Sqlalchemy include empty relationship with join an

Question

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 ?

Answer 1

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