CODEWITHSUNDEEP
node.jssequelize.jssequelize-typescript
rji rji
rji rji

Reputation: 707

Conditional based where condition in sequelize Node JS

I am trying to execute a query in sequelize, in which the where conditions have to be added based on the request parameters

router.get("/getsearchdata",(req, res) => {
    var searchquery =[
        {model:ProvidersCategories,attributes:["category_id"],
        include: [
            {model:Categories,attributes:["category_id","category_name"]},
        ]
        }
        ]  


    if(req.query.agency && req.query.agency!=''){
        searchquery.where = {
        $and: [
        {agencyName: {$like: '%' + req.query.agency + '%'}},
        ]
    }
    }


    Providers.findAll({
        include:searchquery
    }).then(providerData =>{
        if(!providerData) {
        res.status(404).send({reason:'no data found'});
        } else {
        res.status(200).send(providerData);
        }
    });
    });

I have three tables "providers", "categories" and "provider_Categories". I am trying to join the three tables and get the providers. In the request i.e req, if there is an input req.query.agency != '' then an if condition has to added where agencyName like "% + req.query.agency + %", if input req.query.agency == '' then there should be no if condition. For which I have tried the above code. But the where condition is not getting concatenated to the main query even if there is input req.query.agency

What is the right way to write where conditionally

Upvotes: 2

Views: 6871

Answers (1)

Konstantyn Kulakov
Konstantyn Kulakov

Reputation: 116

Your variable searchquery is an array and you can't assign a where property to it. What you should do, is just passing a where object to the query options object:

    router.get("/getsearchdata", (req, res) => {
        var whereClause;

        if (req.query.agency && req.query.agency != '') {
            whereClause = {agencyName: {$like: '%' + req.query.agency + '%'}};
        }


        Providers.findAll({
            include: [
                {
                    model: ProvidersCategories, attributes: ["category_id"],
                    include: [
                        {model: Categories, attributes: ["category_id", "category_name"]},
                    ]
                }
            ],
            where: whereClause
        }).then(providerData => {
            if (!providerData) {
                res.status(404).send({reason: 'no data found'});
            } else {
                res.status(200).send(providerData);
            }
        });
    });

Upvotes: 4

Related Questions