Sequelize Where Associated Model is Null

Question

I have 3 models: Post, Photo, and Audio.

A post can have a photo or an audio clip (or neither):

models.Post.hasOne(models.Photo, { as: 'photo' });
models.Post.hasOne(models.Audio, { as: 'audio' });

I'm trying to select those which have neither - i.e. are just a text post. I've been digging around, and I arrived at (sourcing information from here):

db.Post.findAll({
    where: {
        '$photo.id$': { $eq: null },
        '$audio.id$': { $eq: null }
    },
    order: [
        ['createdAt', 'DESC']
    ],
    include: [{... "photo" and "audio" - both with required: true}],
}).then((posts) => {
    ...
});

however, that doesn't seem to work (I don't think I fully understand how the related models are referenced). In addition, the resulting query uses INNER JOINS instead of LEFT JOINS so when it tries to join Posts and Photos/Audios, it produces an empty result instead of a result with all the values of Post and empty columns for Photo and Audio.

The produced query is:

SELECT ... FROM (
    SELECT *
    FROM "Posts" AS "Post"
    INNER JOIN "Photos" AS "photo"
        ON "Post"."id" = "photo"."postId"
    INNER JOIN "Audios" AS "audio"
        ON "Post"."id" = "audio"."postId"
    WHERE 
        "photo"."id" IS NULL AND
        "audio"."id" IS NULL
) AS ...

The query

SELECT *
FROM "Posts" AS "Post"
LEFT JOIN "Photos" AS "photo"
    ON "Post"."id" = "photo"."postId"
LEFT JOIN "Audios" AS "audio"
    ON "Post"."id" = "audio"."postId"
WHERE 
    "photo"."id" IS NULL AND
    "audio"."id" IS NULL

produces the result set I'm looking for. I think my issue is the include: [{ ... required: true }, ...] flag for each included model - but I'm not sure how to include it for querying but not require it for the result collection.

In my dream world, the sequelize query would look like

db.Post.findAll({ where: { photo: { $eq: null }, audio: ... } })

Answer 1

Use required: false to generate left outer joins.

const lonelyPosts = await db.Post.findAll({
  include: [
    { model: db.Photo, required: false, attributes: [] },
    { model: db.Audio, required: false, attributes: [] },
  ],
  where: [
    Sequelize.where(Sequelize.col('photo.id'), null), 
    Sequelize.where(Sequelize.col('audio.id'), null),
  ],
})