Multi-parameter search with mysql and node.js

Let me preface by saying I’m very new to SQL (and back end design) in general. So for those annoyed with noob questions, please be gentle.

BACKGROUND:

I’m trying to build a product test database (storing test data for all our products) where I want a user to be able to refine a search to find test data they actually want. For example, they may start by searching for all products of a certain brand name, and then refine it with a product type, and/or refine it with a date range of when the test was done.

PROBLEM:

I’m having a hard time finding information on how to implement multi-parameter searches with mysql and node.js. I know you can do nested queries and joins and such within pure SQL syntax, but it’s not abundantly clear to me how I would do this from node.js, especially when certain search criteria aren’t guaranteed to be used.

Ex:

CREATE PROCEDURE `procedureName`(
    IN brandname VARCHAR(20),
       producttype VARCHAR(30))
BEGIN
    SELECT * FROM products
    WHERE brand = brandname
    AND product_type = producttype;
END

I know how to pass data from node.js to this procedure, but what if the user didn’t specify a product type? Is there a way to nullify this part of the query? Something like: AND product_type = ALL;

WHAT I’VE TRIED:

I’ve also looked into nesting multiple SQL procedures, but passing in dynamic data to the “FROM” clause doesn’t seem to be possible. Ex: if I had a brandname procedure, and a product type procedure, I don’t know how/if I can pass the results from one procedure to the “FROM” clause of the other to actually refine the search.

One idea was to create tables with the results in each of these procedures, and pass those new table names to subsequent procedures, but that strikes me as an inefficient way to do this (Am I wrong? Is this a completely legit way to do this?).

I’m also looking into building a query string on the node side that would intelligently decide what search criteria have been specified by the front end, and figure out where to put SQL AND’s and JOIN’s and what-nots. The example below actually works, but this seems like it could get ugly quick as I add more search criteria, along with JOINS to other tables.

// Build a SQL query based on the parameters in a request URL
// Example request URL: http://localhost:3000/search?brand=brandName&type=productType
function qParams(req) {
    let q = "SELECT * FROM products WHERE ";
    let insert = [];
    if(req.query.brand) {
        brandname = req.query.brand; // get brandname from url request
        q = q + `brand = ?`, // Build brandname part of WHERE clause
        insert.push(brandname); // Add brandname to insert array to be used with query.
    };
    if(req.query.type) {
        productType = req.query.type; // get product type from url request
        insert.length > 0 ? q = q + ' AND ' : q = q; // Decide if this is the first search criteria, add AND if not.
        q = q + 'product_type = ?'; // Add product_type to WHERE clause
        insert.push(productType); // Add product_type variable to insert array.
    }
    // Return query string and variable insert array
    return {
        q: q, 
        insert: insert
    };
};

// Send Query
async function qSend(req, res) {
    const results = await qParams(req); // Call above function, wait for results

    // Send query string and variables to MySQL, send response to browser.
    con.query(results.q, results.insert, (err, rows) => {
        if(err) throw err;
        res.send(rows);
        res.end;
    })
};

// Handle GET request
router.use('/search', qSend);

CONCISE QUESTIONS:

Can I build 1 SQL procedure with all my search criteria as variables, and nullify those variables from node.js if certain criteria aren’t used?
Is there way to nest multiple MySQL procedures so I can pick the procedures applicable to the search criteria?
- Is creating tables of results in a procedure, and passing those new table names to other procedures a reasonable way to do that?
Building the query from scratch in node is working, but it seems bloated. Is there a better way to do this?
Googling “multi-parameter search mysql nodejs” is not producing useful results for my question, i.e. I’m not asking the right question. What is the right question? What do I need to be researching?

Answer

One option is to use coalesce():

SELECT p.* 
FROM products p
WHERE 
    p.brand = COALESCE(:brandname, p.brand)
    AND p.product_type = COALESCE(:producttype, p.producttype);

It may be more efficient do explicit null checks on the parameters:

    SELECT p.* 
FROM products p
WHERE 
    (:brandname IS NULL OR p.brand = :brandname)
    AND (:producttype IS NULL OR p.product_type = :producttype);

Advertisement

Answer