mysql过滤器,基于用户输入的MySQL过滤器查询

My previous question was not giving a clear idea about my problem, that is why I am asking a new question .

Here is my table structure

id,

subject (varchar),

category(varchar),

medium(json),

material(json),

style(json)

User can able to search based on the values in table fields ie if user choose only subject (he can select it from multiple select dropdown) query result contains rows that matches the selected items.

If user selected subject and category query result contains rows that matches the selected items and so on (in short it's a filter search like shopping websites)

The problem is I don't how I can efficiently build the MySQL query based on these inputs because some of the Mysql columns are JSON.

Here is the separate SQL query:

// select rows that contains subject test or test1

SELECT *

FROM `cushbu_art`

WHERE subject IN ('test','test1')

// select rows that contains medium is paper or food

SELECT *

FROM `cushbu_art`

WHERE (JSON_CONTAINS(medium, '["paper"]')

OR JSON_CONTAINS(medium, '["food"]'))

Here is a sample input with two parameters (subject and medium)

{

"subject":["test","test1"],

"medium":["paper","wood"],

"category":"",

"material":"",

"style":""

}

Note: input and output are in JSON (REST API)

db.js

function(req, res) {

var sql = '';

if(req.body.subject) {

sql+=//PREPARE SUBJECT QUERY

}

if(req.body.medium) {

sql+=//PREPARE QUERY

}

//FINAL SQL QUERY HERE;

db.query(sql, function(error, result) {

console.log(result);

});

}

解决方案function(req,res){

var sql = 'SELECT * FROM `cushbu_art`';

//Check if only on is set

if(req.body.subject && !req.body.medium){

var subjectJoin=req.body.subject.join(',');

sql+='WHERE subject IN ('+subjectJoin+')';

}

//Check if only on is set

if(req.body.medium && !req.body.subject){

sql+='WHERE (JSON_CONTAINS(medium,'+req.body.medium[0]+') OR JSON_CONTAINS(medium,'+req.body.medium[1]+')';

}

if(req.body.medium && req.body.subject){

var subjectJoin=req.body.subject.join(',');

sql+='WHERE subject IN ('+subjectJoin+') AND (JSON_CONTAINS(medium,'+req.body.medium[0]+') OR JSON_CONTAINS(medium,'+req.body.medium[1]+')';

}

//FINAL SQL QUERY HERE';

db.query(sql,function(error,result){

console.log(result);

});

}