CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
Pyae Phyoe Shein
Pyae Phyoe Shein

Reputation: 13787

Error: where: "raw query" has been removed, please use where ["raw query", [replacements]]

Unhandled rejection Error: where: "raw query" has been removed, please use where ["raw query", [replacements]]

I encountered this error while rendering the following code. It's a dynamic where clause, generated by the value entered by user in the search field:

var queryWhere = {id: {$ne: null}};
if (req.query) {
    if (req.query.gender && req.query.gender !== '') {
        searchGender = "gender = '" + req.query.gender + "'";
    } else if (req.query.gender && req.query.gender === '') {
        searchGender = "gender IS NOT NULL ";
    }
    if (req.query.experience && req.query.experience !== '') {
        searchExperience = "experience = '" + req.query.experience + "'";
    } else if (req.query.gender && req.query.experience === '') {
        searchExperience = "experience IS NOT NULL ";
    }
    queryWhere = {
        $and: [
            {$or: [
                searchGender,
                searchExperience]}
        ]
    };

    models.Users.findAll({
        offset: numPerPage * 50, 
        limit: 50,
        where: queryWhere,
        include: [
            {model: models.Users_Answers}
        ],
        order: [
            [models.Sequelize.col('id'), 'ASC'],
            [models.Users_Answers, 'id', 'ASC']
        ]
    }).then(function(answers) {
        res.render('answers', {answers: answers, search: req.query.searchParam, moment: moment, pagesize: (numPerPage+1), total: totalCount / 50});
    });
} else {
    res.render('answers');
}

How can I resolve this?

Upvotes: 4

Views: 8306

Answers (3)

Vadivel Subramanian
Vadivel Subramanian

Reputation: 458

Recommended using Sequelize's own operators

var defered = Q.defer();
const offset = queryString.offset * queryString.limit;
const limit = queryString.limit;
var queryWhere = { class_id: { $ne: null }, section_id: { $ne: null } };
var searchClass = {};
var searchSection = {};
if (queryString) {
    if (queryString.class && queryString.class !== "") {
       searchClass = { class_id: { $eq: queryString.class } };
    } else if (queryString.class && queryString.class === "") {
       searchClass = { class_id: { $ne: null } };
    }

if (queryString.section && queryString.section !== "") {
      searchSection = { section_id: { $eq: queryString.section } };
} else if (queryString.section && queryString.section === "") {
      searchSection = { section_id: { $ne: null } };
}
}

queryWhere = {
    $and: [[searchClass], [searchSection]]
};
const schoolDB = require("../../db/models/tenant")(schema);
const Student = schoolDB.model("Student");
Student.findAll({
   attributes: [
  "id",
  "first_name",
  "last_name",
  "profile_image_url",
  "roll_number",
  "emergency_contact_number"
],
offset: offset,
limit: limit,
where: queryWhere,
order: [["roll_number", "ASC"]]
})
.then(result => {
  defered.resolve(result);
})
.catch(err => {
  defered.reject(err);
});

Upvotes: 0

Yrysbek Tilekbekov
Yrysbek Tilekbekov

Reputation: 2775

Recommended way is using Sequelize's own operators

if (req.query.gender && req.query.gender !== '') {
    searchGender = {'gender': req.query.gender};
} else if (req.query.gender && req.query.gender === '') {
    searchGender = {'gender': {$not: null}};
}
if (req.query.experience && req.query.experience !== '') {
    searchExperience = {'experience': req.query.experience};
} else if (req.query.gender && req.query.experience === '') {
    searchExperience = {'experience': {$not: null}};
}

Upvotes: 1

FiftiN
FiftiN

Reputation: 797

Raw query should be array

queryWhere = {
    $and: [
        {$or: [
            [searchGender],
            [searchExperience]]}
    ]
};

Upvotes: 5

Related Questions