node.js sequelize: multiple 'where' query conditions

Question

How do i query a table with multiple conditions? Here are the examples both working:

Post.findAll({ where: ['deletedAt IS NULL'] }).success()

and

Post.findAll({ where: {topicId: req.params.id} }).success()

Then, if i need conditions combined, i feel like i need to do something like

Post.findAll({ where: [{topicId: req.params.id}, 'deletedAt IS NULL'] }).success()

, but it doesn't work.
What is the syntax the sequelize waits for?

node debug says:

DEBUG: TypeError: Object # has no method 'replace'

if it matters...

Diosney · Accepted Answer · 2016-09-23 15:07:32Z

29

You can do:

Post.findAll({ where: {deletedAt: null, topicId: req.params.id} })

Which would be translated to deletedAt IS NULL

BTW, if you need deletedAt NOT IS NULL, use:

Post.findAll({ where: {deletedAt: {$ne: null}, topicId: req.params.id} })

edited Sep 23, 2016 at 15:07

Diosney

10.6k15 gold badges70 silver badges113 bronze badges

answered Jul 15, 2014 at 11:01

gwae

2913 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

H. Mahida Over a year ago

Please update your answer to check multiple where, show are only showing one condition where as he needs to check number and null.

Rahil Wazir · Accepted Answer · 2014-11-17 13:41:40Z

12

Just do:

Post.findAll(
    { where: ["topicId = ? AND deletedAt IS NULL", req.params.id] }
).success()

Or do you need an or query?

edited Nov 17, 2014 at 13:41

Rahil Wazir

10.1k11 gold badges46 silver badges66 bronze badges

answered May 31, 2012 at 20:46

sdepold

6,2262 gold badges43 silver badges47 bronze badges

3 Comments

Masquer Over a year ago

Its AND that i needed, and your solution perfectly fits my needs, so thank you.

Masquer Over a year ago

I must say its kinda obvious what u have done there, how could I not have done so myself?) But it feels more like workaround, I'm still curious what would I do if both conditions had parameters? Or if there'd be more conditions? Sequelize manuals doesn't have anything on such cases...

Masquer Over a year ago

Nvm, i just found out that there can be several parameters provided subsequently for '?'s. Thanks again)

thatguy1155 · Accepted Answer · 2021-07-09 08:21:42Z

10

Please note that as of this posting and the 2015 version of sequelize, the above answers are out of date. I am posting the solution that I got working in hopes of saving someone some time.

filters["State"] = {$and: [filters["State"], {$not: this.filterSBM()}] };

Note the $ and array inside the JSON object and lack of ? token replacement.

Or put in a more general way, since that might help someone wrap their head around it:

{ $and: [{"Key1": "Value1"}, {"Key2": "Value2"}] }

edited Jul 9, 2021 at 8:21

thatguy1155

1021 silver badge8 bronze badges

answered Apr 29, 2015 at 18:52

JR Smith

1,21616 silver badges22 bronze badges

2 Comments

NOMO Over a year ago

Instead of mentioning the most recent version of sequelize, it would be useful if you quote the exact version. When I read your 2015 post in 2020, fairly sure the version you referenced is not the most recent version anymore.

JR Smith Over a year ago

Reasonable. I also wrote that off the cuff while on the job I had 5 years ago, but I'll bear that in mind.

Boris · Accepted Answer · 2020-04-15 05:35:11Z

8

This gives me SELECT `id`, `title`, `description`, `test`, `published`, `createdAt`, `updatedAt` FROM `tests` AS `test` WHERE (`test`.`title` LIKE '%3%' OR `test`.`description` LIKE '%2%');

exports.findAll = (req, res) => {
    const title = req.query.title;
    const description = req.query.description;
    Tutorial.findAll({
            where: {
                [Op.or]: [{
                        title: {
                            [Op.like]: `%${title}%`
                        }
                    },
                    {
                        description: {
                            [Op.like]: `%${description}%`
                        }
                    }
                ]
            }
        })
        .then(data => {
            res.send(data);
        })
        .catch(err => {
            res.status(500).send({
                message: err.message || "Some error occurred while retrieving tutorials."
            });
        });

};

edited Apr 15, 2020 at 5:35

answered Apr 14, 2020 at 21:49

Boris

1591 silver badge8 bronze badges

1 Comment

Lucio Mollinedo Over a year ago

This is the right answer. For some reason, most of the people above provided answers for AND condition. This works in Sequelize 6.

Adiii · Accepted Answer · 2017-02-03 10:05:59Z

7

In new version try this

Post.findAll({
  where: {
    authorId: 12,
    status: 'active'
  }
}).then(function (data) {
    res.status(200).json(data)
            })
   .catch(function (error) {
                res.status(500).json(error)
   });;

edited Feb 3, 2017 at 10:05

answered Feb 1, 2017 at 22:08

Adiii

61.1k10 gold badges180 silver badges174 bronze badges

Collectives™ on Stack Overflow

node.js sequelize: multiple 'where' query conditions

5 Answers 5

1 Comment

3 Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

3 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related