Sequelize MySql equivalent ORM query

Question

I have a row query like this

SELECT d.title
FROM DEALS d
LEFT JOIN USER u ON u.idUser = d.userId 
WHERE ((d.title LIKE  '%gouthamkrishna%' OR d.keywords LIKE  '%gouthamkrishna%')
OR CONCAT(u.firstName,'',u.lastName) LIKE  '%gouthamkrishna%') AND d.isPublic=1

I want to build an equivalent ORM query based on it Here is my current query

//deals table
const Deals = require('deals');
//user table
const User = require('user');
Deals.findAndCountAll({
        where:{
            isPublic:1,
            [Op.or]:[
                {title: { $like: '%' + params.keyword + '%' }},
                {keywords: { $like: '%' + params.keyword + '%' }}
            ]
       }, attributes:['title'],
       include:[
        {model:User, required:false,attributes:['firstName'],
        where:Sequelize.where(Sequelize.fn('concat_ws', Sequelize.col('firstName'), ' ', Sequelize.col('lastName')), {
                $like: '%' + params.keyword + '%'
                })

        }]

    })

But this will return the empty result however the row query returns result

the equivalent query generated from ORM

SELECT `DEALS`.`idDeal`, `DEALS`.`title`, `USER`.`idUser` AS `USER.idUser`, `USER`.`firstName` AS `USER.firstName` FROM `DEALS` AS `DEALS` LEFT OUTER JOIN `USER` AS `USER` ON `DEALS`.`userId` = `USER`.`idUser` AND concat_ws(`firstName`, ' ', `lastName`) LIKE '%gouthamkrishna%' WHERE (`DEALS`.`title` LIKE '%gouthamkrishna%' OR `DEALS`.`keywords` LIKE '%gouthamkrishna%') AND `DEALS`.`isPublic` = 1;

No errors but i am getting empty rows while the row query returns the results — iam batman

Vivek Doshi Vivek Doshi · Accepted Answer · 2018-05-28T04:34:21

I think , you need to just modify where query a bit , try this :

Deals.findAndCountAll({
    where:{
            isPublic:1,
            [Op.or]:[
                {title: { $like: '%' + params.keyword + '%' }},
                {keywords: { $like: '%' + params.keyword + '%' }},
                Sequelize.where(Sequelize.fn('concat_ws', Sequelize.col('firstName'), ' ', Sequelize.col('lastName')), {
                    $like: '%' + params.keyword + '%'
                })
            ]
    }, 
    attributes:['title'],
    include:[
        {
            model:User, 
            required:false,
            attributes:['firstName'],
    }]
})

Sequelize MySql equivalent ORM query

1 Answers