基于用户输入的MySQL过滤器查询 [英] Mysql filter query based on user inputs

问题描述

我之前的问题没有给出明确的想法关于我的问题，这就是为什么我要问一个新问题.

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

这是我的表结构

id,
subject (varchar),
category(varchar),
medium(json),
material(json),
style(json)

用户可以基于表字段中的值进行搜索，即，如果用户仅选择subject(他可以从多个选择下拉列表中进行选择)，则查询结果中包含与所选项目匹配的行.

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)

问题是我无法根据这些输入有效地构建MySQL查询，因为某些Mysql列是JSON.

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.

这是单独的SQL查询:

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"]'))

这是带有两个参数(subject和medium)的样本输入

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

{
    "subject":["test","test1"],
    "medium":["paper","wood"],
    "category":"",
    "material":"",
    "style":""
}

注意:输入和输出均以JSON(REST API)

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);
 });

}

这篇关于基于用户输入的MySQL过滤器查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！