Querying combinations of endpoint parameters with Sequelize in Express.js

Question

Using the Sequelize ORM in Express.js to query a mySQL DB.

In Express.js I have an endpoint which takes two parameters 'level' and 'topic', like this,

//Get Topic
router.get("/tasks/:level&:topic", (req, res) => {
  Task.findAll({
      where: {
        Topic: req.params.topic,
        Level: req.params.level
      }
    })
    .then(tasks => {
      res.json(tasks)
    })
    .catch(err => {
      res.send("error: " + err)
    })
})

This works fine for when I want to query for 'level AND topic'.

But sometime 'level' might be empty and I only want to query 'topic' and at other times 'topic' might be empty and I only want to query 'level'.

I would like to have many parameters in this endpoint not just the two here and be able to query any combination of those parameters.

I imagine if this is not possible that I would have to create an endpoint for every combination of parameters. Is there an easier way to do this?

score 2 · Answer 1 · edited Oct 29 '21 at 02:49

I'm two years late. But I had the same issue these last days and I think the way that I resolved it can help others. There's an operator in Sequelize called [Op.or]. In these case:

//Get Topic
router.get('/tasks', (req, res) => {
    Task.findAll({
        where: {
            [Op.or]: {
                Topic: req.query.topic,  // /tasks?topic=topic
                Level: req.query.level   // /tasks?level=level
            }
        }
    })
    .then(tasks => {
        res.json(tasks)
    })
    .catch(err => {
        res.send("error: " + err)
    })
})

And maybe consider the [Op.like] to match the queries:

//Get Topic
router.get('/tasks', (req, res) => {
    Task.findAll({
        where: {
            [Op.or]: {
                Topic: { [Op.like]: '%' + req.query.topic + '%' },  
                Level: { [Op.like]: '%' + req.query.level + '%' }   
            }
        }
    })
    .then(tasks => {
        res.json(tasks)
    })
    .catch(err => {
        res.send("error: " + err)
    })
})

score 1 · Answer 2 · answered Aug 14 '19 at 19:53

1

If its simple field= value query then you can go with the below one in this case,

in below case you have to keep params name same as your database field name

router.get("/tasks/:Level&:Topic", (req, res) => { //<--- CHANGE
    Task.findAll({
            where : req.params //<--- CHANGE
        })
        .then(tasks => {
            res.json(tasks)
        })
        .catch(err => {
            res.send("error: " + err)
        })
})

Other option is to check the req.params and make/create where query as per your need

answered Aug 14 '19 at 19:53

Vivek Doshi

56,649
12
110
122

thanks for your reply, but I am trying to figure out how to handle a combination of parameters where empty ones are not to be part of the query. – Shane G Aug 14 '19 at 20:33
1

In that case you have to loop through params and create the create the query as per your need, there is no other way around. – Vivek Doshi Aug 14 '19 at 20:35
Yep that worked by looping through as you suggested, thanks – Shane G Aug 15 '19 at 13:10
1

@ShaneG, glad to know :) – Vivek Doshi Aug 15 '19 at 13:22

score 0 · Answer 3 · answered Aug 14 '19 at 23:00

I got this work by creating an object called query which I added elements to depending on whether they were empty or not.

Then I used this object as the 'where' condition in the query,

//Get Query
router.get("/tasks/:level?/:topic?", (req, res) => {
  var query = {};
  if (req.params.topic != " ") {
    console.log("topic sent");
    query.Topic = req.params.topic;
  }
  if (req.params.level != " ") {
    console.log("level sent");
    query.Level = req.params.level;
  }
  console.log(query);
  Task.findAll({
      where: query
    })
    .then(tasks => {
      res.json(tasks)
    })
    .catch(err => {
      res.send("error: " + err)
    })
})

In Vue.js the parameters are sent using this method,

getQuery(level, topic) {
      if (!topic) {
        topic = " ";
      }
      if (!level) {
        level = " ";
      }
      axios.get("/api/tasks/" + level + "/" + topic + "/").then(
        // axios.put('/api/task/' + id, {
        result => {
          console.log(result.data)
          this.todos = result.data
        },
        error => {
          console.error(error)
        }
      )
    },

This will allow me to handle any combination of parameters from the endpoint.]

The '?' in the url I found out about in this post https://stackoverflow.com/a/41736911/4001324 which allows the parameters to be optional. Although here they are always passed, and an a space if empty.

If only 'topic' is sent then the query created could be { Topic: 'Mechanics' }, if only level is sent the query could be { Level: 'Higher' } and if both are sent the query created could be { Topic: 'Mechanics' , Level: 'Higher' }. So getQuery() always receives two parameters one of these combinations getQuery(" ",topic) or getQuery(level," ") or getQuery(level,topic) so basically a missing parameter is passed as a space " ". So if the parameter is not represented by a space " " then it is added to the query object. Does that explain it? — Shane G, Aug 15 '19 at 21:38

Querying combinations of endpoint parameters with Sequelize in Express.js

3 Answers3