Dynamic search query in MySQL

Question

Here is the project installation:
1. Nodejs server side environment
2. MySQL database (with Knexjs query builder)
3. 'locations' table in a database with fields like 'country', 'city', 'category', 'working_hours'.
4. Form input where user can select all these values and submit the search. When the user selects nothing, it defaults to 'ALL'. I need to filter the database and to select rows corresponding to the user search input.

I tried to do something like this:

knex('locations').where({
   country: countryInput,
   city: cityInput,
   category: categoryInput,
   working_hours: hoursInput
   })
   .then(function(rows) {.....})
   .catch(function(err) {.....});

This works well if user selects all the input fields, what if the user selects only few of them and other are set to 'all'? How to make this work?

Thank you in advance!

[Can I conditionally add a where() clause to my knex query?](https://stackoverflow.com/questions/27232415/can-i-conditionally-add-a-where-clause-to-my-knex-query) — Solarflare, Jul 10 '17 at 07:51
You can provide a callback to the 'where' method: `knex('users').where(function() { if (condition) { this.where('name', 'John'} else..... })` — Igorko, Jul 10 '17 at 09:21

score 2 · Answer 1 · answered Jul 10 '17 at 10:06

2

This is pretty common solution:

let query = knex('locations');

if (countryInput) {
  query = query.where('country', countryInput);
}

if (cityInput) {
  query = query.where('city', cityInput);
}

if (categoryInput) {
  query = query.where('category', categoryInput);
}

if (hoursInput) {
  query = query.where('working_hours', hoursInput);
}

query
  .then(function(rows) {.....})
  .catch(function(err) {.....});

answered Jul 10 '17 at 10:06

Mikael Lepistö

18,909
3
68
70

Thank you! This is exactly what I was looking for! – Igorko Jul 10 '17 at 13:23

score 0 · Answer 2 · answered Jul 10 '17 at 12:25

you can modify your query using Modify

expecting req.query object matching your table columns-

knex('locations')
        .modify(function(queryBuilder) {
            if (req.query) {
                queryBuilder.where(req.query);
            }
        }) 
   .then(function(rows) {.....})
   .catch(function(err) {.....});

well, there are some potential risk. You need filter your query first.

Dynamic search query in MySQL

2 Answers2