Sequelize ORM - excluding Left Join

Question

I'm building Full Stack, Social media app using Sequelize ORM.

sequelize: 6.6.5 
sequelize-cli: 6.2.0

My database is built of tables Users, Posts and ReadPosts - which has two foreign keys - UserId and PostId.

One of the features of the app, is that user can easily see new posts, that have not been read by him/her yet, so every time user reads a post it generates a row in ReadPost table, made of UserId (who read the post) and PostId (which was read by the user).

What I'm trying to do now, is to display all posts that have not been read, so it would be some kind of excluding left join, that would get all existing posts, and exclude those pots from ReadPost with given userId, but I can't figure out how to do it with Sequlize.

ReadPost model:

module.exports = (sequelize) => {
    const readPost = sequelize.define("ReadPost", {})

    readPost.associate = models => {
        readPost.belongsTo(models.User, {
            foreignKey: {
                allowNull: false
            },
            onDelete: "CASCADE",
            foreignKeyConstrains: true
        })
        readPost.belongsTo(models.Post, {
            foreignKey: {
                allowNull: false
            },
            onDelete: "CASCADE",
            foreignKeyConstrains: true
        })
    }
    return readPost
}

I know I could do it virtually and just run findAll() on posts, and not display those that have not been read yet, depending on some javascript flag or simply class, but this is a project for my portfolio so I want to do it properly. Can I have some help please?

@Anatoly

I had to play a bit with your code, as I'm using newer version of sequelize and got something like this:

 exports.showAllUnreadPosts = (req, res, next) => {
    db.Post.findAll({
        where: {
            "ReadPost.id": null,
            userId: res.locals.userId //userId extracted from authorization middleware
        },
        include: [{
            model: db.ReadPost,
            required: false,
            attributes: []
        }]
    }) 

with that it retuns

"error": "Error SequelizeDatabaseError: Unknown column 'Post.ReadPost.id' in 'where clause'"

I tried to understand the line '"ReadPost.id": null', but as far as I understand sql syntax it would be looking for that column in Post table? I don't have such column, relation exists in ReadPost table, where it gathers userIds and postIds, not sure if my implementation is clear

Just in summary - I need to get all existing posts from Post table and compare it with ReadPost table, where postId and userId are stored. So probably I'd have to run findAll on Posts, findAll on ReadPost with current userId, and exclude all those postIds recorded in ReadPost from Post.findAll

Have a look screenshoot of how currently data looks like in those tables: picture of DB tables

So baisically I need Post.findAll() + ReadPost.findAll() where userId: res.locals.userId and return all posts from Post table but do not exist with that ReadPost query.

I hope that makes it more clear.

@Anatoly 11/03/22 Query works now, but returns only posts that have not been read by ANY user (row doesn't exist) and the user is the author of the post.

What I managed to do for now, is get all posts that have been read by the user (code below). I need exact opposite of it (row doesn't exist or it exists, but not with this userId)

So, from all posts that exist, Select those read by user, and exclude those else from all of the posts in the DB

exports.showAllUnreadPosts = (req, res, next) => {
    db.Post.findAll({
        where: {
            '$readposts.UserId$': res.locals.userId // User Id extracted from auth middleware
        },
        include: [{
            model: db.ReadPost,
            required: false,
            attributes: [],
        }]
    }).then(unreadPosts => {
        res.status(200).json(unreadPosts);
    })
        .catch((error) => {
            res.status(500).json({
                error: 'Error ' + error
            })
        })
}

Can you please advise?

Answer 1

You can query all posts that doesn't have a link record in ReadPost by adding a condition Readpost.id is null like this:

const unreadPosts = await Post.findAll({
  where: {
    '$ReadPost.id$': null,
    userId: userId
  },
  include: [{
    model: ReadPost,
    required: false,
    attributes: [],
    where: {
      UserId: userId
    },
  }]
})

And of course, you need to add an association from Post to ReadPost:

Post.hasMany(ReadPost, <some_options_here>);

Answer 2

Right, it seems like I found solution with a great help from @Anatoly.

I'm not sure, if it's a good idea, since I added a second method in the THEN block, I'm happy to get any feedback on it.

exports.showAllUnreadPosts = (req, res, next) => {
    db.Post.findAll({
        where: {
            '$readposts.UserId$': res.locals.userId // User Id extracted from auth middleware
        },
        attributes: ['id'],
        include: [{
            model: db.ReadPost,
            required: false,
            attributes: [],
        }]
    }).then(readPosts => {
        db.Post.findAll({
            where: {
                id: {
                    [Op.not]: (() => readPosts.map(readPost => readPost.id))()
                }
            }
        })
            .then((unreadPosts) => {
                res.status(200).json(unreadPosts);
            })
            .catch((error) => {
                res.status(500).json({
                    error: 'Error' + error
                })
            })

    })
        .catch((error) => {
            res.status(500).json({
                error: 'Error ' + error
            })
        })
}

First of all, there is a method that checks all the posts that are already read by the user in readpost table and returns post ids. Secondly in the THEN block, it gets all the existing posts in the db, and excludes those with ids from above method (by [OP.not]). I hope it makes sense, not sure about performance.