Convert SQL query to Sequelize Query Format

Question

I'm new to Sequelize ORM. I would like to convert SQL query to Sequelize Query.

This is my SQL query, I want to convert this query to sequelize query:

SELECT * FROM `Posts` AS `Posts` 
    WHERE `Posts`.user_id IN 
        (SELECT `Follows`.receiver_id FROM `follows` AS `Follows` 
            WHERE `Follows`.user_id = user_id and `Follows`.status = "accept");

I have tried this but it does not return any data:

Posts
    .findAll({ where: { 
        user_id: { [Op.in]: [{
                include: [{
                    model: Follows,
                    attributes: ['receiver_id'],
                    where: { 
                        user_id: user_id,
                        status:status
                    }
                }]
            }]
        }
    }})
    .then(users => { res.send(users); })

After Executing above code it gives error in console

SELECT `event_id`, `user_id`, `event_message`, `e_imagepath`, 
    `createdAt`, `updatedAt`, `receiver_id` 
    FROM `Posts` AS `Posts` 
    WHERE `Posts`.`user_id` IN ('[object Object]');

I would like to convert SQL query to Sequelize Query.

score 0 · Answer 1 · answered Jul 07 '19 at 06:22

You put your incude in the wrong position. Sequelize does not have a subquery feature as far I am aware of.

So you could do instead:

Posts
    .findAll({ where: { user_id: user_id},
               include: [{
                    model: Follows,
                    attributes: ['receiver_id'],
                    where: { 
                        user_id: user_id,
                        status:status
                    }
                }]

    })
    .then(users => { res.send(users); })

If the example above does not suits your need. You can also try to use a subquery by mixing raw SQL with Sequelize as the link below describes:

stackoverflow.com/questions/28286811/sequelize-subquery-as-field

I have tried this but this returns data based on user_id. Actually it should return data based on receiver_id which I'm getting from Follows table. — cool, Jul 07 '19 at 07:41
Something Like this.....where:{user_id:{[Op.in]:[sequelize.literal('SELECT `Follows`.receiver_id FROM `follows` AS `Follows` WHERE `Follows`.user_id=1 and `Follows`.status="accept')]}} — cool, Jul 07 '19 at 07:50

score 0 · Accepted Answer · answered Jul 07 '19 at 07:47

This works fine.

router.get('/posts', function(req, res)
{
    const user_id = req.session.user_id;
    const status = "accept";
    Posts.findAndCountAll({include:[{ model: Likes},{ model: Comments},{ model: Users}],
        where:{user_id:{[Op.in]:[sequelize.literal('SELECT `Follows`.receiver_id FROM `follows` AS `Follows` WHERE `Follows`.user_id=1 and `Follows`.status="accept')]}}

        })
    .then((postdata)=>
    {

        Users.findAll({where:{user_id:user_id}})
        .then((userdata)=>
        {
            res.send(postdata.rows)
           // res.render('home',{title:'home',items:postdata.rows,user:userdata});
        }) 
        .catch((err)=>
        {

        })

    })
    .catch((err)=>
    {

    })


});

Convert SQL query to Sequelize Query Format

2 Answers2