How can i use the querystring for knex?

Question

router.get("/stocks/symbols", function (req, res, next) {
  req.db
    .from("stocks")
    .select("name","symbol","industry")
    .modify(function(queryBuilder) {
      if (req.query.param) {
          queryBuilder.where('industry',req.query.param);
      }
  })
    .where('timestamp', '=', '2020-03-24T00:00:00Z')
    .then((rows) => {
      res.json(rows)
    })
    .catch((err) => {
      console.log(err)
      res.json({ Error: false, Message: "Error in MySQL query" })
    })
})

I'm trying to make such a functionality to use querystring. At the moment this runs without error but doesn't do anything. Whether I put ?industry=h or anything after the route, it returns the same data. There was an example I followed, but for some reason its not working. What else am i missing?

router.get("/stocks/symbols/:industry", function (req, res, next) {
  req.db
    .from("stocks")
    .select("name","symbol","industry")
    .where('timestamp', '=', '2020-03-24T00:00:00Z')
    .where("industry", "like", `%${req.params.industry}%`)
    .then((rows) => {
      res.json({ Error: false, Message: "Success", Cities: rows })
    })
    .catch((err) => {
      console.log(err)
      res.json({ Error: true, Message: "Error in MySQL query" })
    })
})

This does similar job that i want to do but it doesn't use the querystring.

https://stackoverflow.com/questions/27232415/can-i-conditionally-add-a-where-clause-to-my-knex-query — Naknak, Jun 11 '20 at 16:54

score 0 · Accepted Answer · answered Jun 11 '20 at 16:59

0

You need to apply the correct query parameter - in your code you use query.param meaning that your url would need to contain /stocks/symbols?param=tbd

If you're expecting industry as a query-param, you need to change it to:

.modify(function(queryBuilder) {
      if (req.query.industry) {
          queryBuilder.where('industry',req.query.industry);
      }
  })

answered Jun 11 '20 at 16:59

eol

23,236
5
46
64

Its partly working, but the problem happening right now is that I need to put the exact value to get answer. If I just put "h", it doesn't return anything and if i put "health care"(exact name) it works. – Naknak Jun 11 '20 at 17:08
1

I solved it using the same method queryBuilder.where('industry',"like",`%${req.query.industry}%` – Naknak Jun 11 '20 at 17:11

How can i use the querystring for knex?

1 Answers1