jqGrid的高级搜索 - 我们能不能使用＆QUOT;和＆QUOT;和＆QUOT;或QUOT;运营商在同一时间？ [英] JQGrid Advance Search - Could we use "AND" and "OR" operators at the same time?
问题描述
我使用的jqGrid高级搜索功能 multipleSearch：真的，multipleGroup：真正的。
我使用Asp.net MVC和经典ado.net +存储过程也。
每当在JGRID用户搜索数据，我将通过这个搜索标准storeprocedure参数值。如... SELECT * FROM tableName值在哪里@WhereClauseDynamic 。所以，我创建了Where子句生成器类。
I am using JQGrid Advance Search feature multipleSearch: true, multipleGroup: true .
I am using Asp.net MVC and classic ado.net + Stored procedure also.
Whenever user search data at JGRID, I will pass this searching criteria to storeprocedure as parameter values. Such as ... Select * from tableName Where @WhereClauseDynamic . So I have created "Where Clause Generator" Class.
namespace MvcMobile.ViewModel
{
[ModelBinder(typeof(GridModelBinder))]
public class JqGrid_Setting_VewModel
{
public bool IsSearch { get; set; }
public int PageSize { get; set; }
public int PageIndex { get; set; }
public string SortColumn { get; set; }
public string SortOrder { get; set; }
public string Where { get; set; }
}
public class WhereClauseGenerator
{
private static readonly string[] FormatMapping = {
" ({0} = '{1}') ", // "eq" - equal
" ({0} <> {1}) ", // "ne" - not equal
" ({0} < {1}) ", // "lt" - less than
" ({0} <= {1}) ", // "le" - less than or equal to
" ({0} > {1}) ", // "gt" - greater than
" ({0} >= {1}) ", // "ge" - greater than or equal to
" ({0} LIKE '{1}%') ", // "bw" - begins with
" ({0} NOT LIKE '{1}%') ", // "bn" - does not begin with
" ({0} LIKE '%{1}') ", // "ew" - ends with
" ({0} NOT LIKE '%{1}') ", // "en" - does not end with
" ({0} LIKE '%{1}%') ", // "cn" - contains
" ({0} NOT LIKE '%{1}%') " // "nc" - does not contain
};
public string Generator(Filter _Filter)
{
var sb = new StringBuilder();
foreach (Rule rule in _Filter.rules)
{
if (sb.Length != 0)
sb.Append(_Filter.groupOp);
sb.AppendFormat(FormatMapping[(int)rule.op], rule.field, rule.data);
}
return sb.ToString();
}
}
public class GridModelBinder : IModelBinder
{
public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
{
try
{
var request = controllerContext.HttpContext.Request;
var serializer = new JavaScriptSerializer();
var _WhereClauseGenerator = new WhereClauseGenerator();
var _IsSearch = bool.Parse(request["_search"] ?? "false");
var _PageIndex = int.Parse(request["page"] ?? "1");
var _PageSize = int.Parse(request["rows"] ?? "10");
var _SortColumn = request["sidx"] ?? "";
var _SortOrder = request["sord"] ?? "asc";
var _Where = request["filters"] ?? "";
return new JqGrid_Setting_VewModel
{
IsSearch = _IsSearch,
PageIndex = _PageIndex,
PageSize = _PageSize,
SortColumn = _SortColumn,
SortOrder = _SortOrder,
Where = (_IsSearch == false || string.IsNullOrEmpty(_Where)) ? string.Empty : _WhereClauseGenerator.Generator(serializer.Deserialize<Filter>(_Where))
};
}
catch
{
return null;
}
}
}
[DataContract]
public class Filter
{
[DataMember]
public GroupOp groupOp { get; set; }
[DataMember]
public List<Rule> rules { get; set; }
}
[DataContract]
public class Rule
{
[DataMember]
public string field { get; set; }
[DataMember]
public Operations op { get; set; }
[DataMember]
public string data { get; set; }
}
public enum GroupOp
{
AND,
OR
}
public enum Operations
{
eq, // "equal"
ne, // "not equal"
lt, // "less"
le, // "less or equal"
gt, // "greater"
ge, // "greater or equal"
bw, // "begins with"
bn, // "does not begin with"
//in, // "in"
//ni, // "not in"
ew, // "ends with"
en, // "does not end with"
cn, // "contains"
nc // "does not contain"
}
}
通过使用上code，一切都是正确的，当我搜索像
By using upper code, everything is correct when I search like that
{
"groupOp":"AND",
"rules":[{"field":"Seminar_Code","op":"eq","data":"MED01"},
{"field":"Seminar_Code","op":"eq","data":"CMP05"}],"groups":[]
}
sb.ToString() // Output vlaue
" (Seminar_Code = 'MED01') AND (Seminar_Code = 'CMP05') "
所以，这是完全正确的。
So, It is totally correct.
但是，当它来到更复杂的搜索查询这样的...
But when it come to more complex search query like that ...
{
"groupOp":"AND",
"rules":[{"field":"Seminar_Code","op":"eq","data":"MED01"},
{"field":"Seminar_Code","op":"eq","data":"CMP05"}],
"groups":[{
"groupOp":"OR",
"rules": [{"field":"Seminar_Code","op":"eq","data":"CMP01"}],"groups":[]}]
}
sb.ToString() // Actual Output value is like that below
" (Seminar_Code = 'MED01') AND (Seminar_Code = 'CMP05') "
但是我预期的是象下面这样。
But what I had expected is like that below ..
" ((Seminar_Code = 'MED01') AND (Seminar_Code = 'CMP05')) OR ( Seminar_Code = 'CMP01' ) "
所以，我怎么能做到这一点是正确的。
So How could I do it correct.
是jqGrid的支持多组操作，如和+或？这是只支持一个运营商在同一时间？我们可以用AND和或O preators在同一时间？
每一个建议将是AP preciated。
Every suggestion will be appreciated.
推荐答案
首先我要指出，我觉得危险，你使用code。您构建要在选择使用WHERE建设和使用的信任的输入数据。您可以接收SQL注入的问题。你应该写你的code更安全。你需要逃生适用于所有[，％和 _ 其中包含运营商 LIKE 。
First of all I should mention that I find dangerous the code which you use. You construct the WHERE construction which you want to use in SELECT and you use trust the input data. You can receive SQL Injection problem. You should write your code much more safe. You need escape all [, % and _ used in operators which contains LIKE.
此外，我会建议你使用SELECT与参数。而不是
Moreover I would recommend you to use SELECT with parameters. Instead of
Seminar_Code LIKE 'MED01%'
您可以使用
Seminar_Code LIKE (@p1 + '%')
和使用 SqlCommand.Parameters 定义 @ P1 和您所使用的其他参数。
and use SqlCommand.Parameters to define the value of @p1 and other parameters which you use.
现在我试着回答您的主要问题。 的定义过滤器，你使用不使用类。您应该延长组输入部分过滤器类的东西像
Now I try to answer on your main question. The definition of Filter class which you use don't use groups part on the input. You should extend Filter class to something like
public class Filter {
public GroupOp groupOp { get; set; }
public List<Rule> rules { get; set; }
public List<Filter> groups { get; set; }
}
您还应该延长 WhereClauseGenerator.Generator 方法code分析组部分。我建议你还用的名字更贴近标准名称转换。如果你使用像 _Filter 的名称为变量而不是类的私有成员它使code misundertandable。此外，该级 WhereClauseGenerator 可以是静态的（公共静态类WhereClauseGenerator ）和方法发电机了。
You should also extend the code of the WhereClauseGenerator.Generator method to analyse the groups part. I recommend you additionally to use names more close to the standard name conversion. If you use the names like _Filter for the variable and not for the private members of the class it make the code misundertandable. Moreover the class WhereClauseGenerator can be static (public static class WhereClauseGenerator) and the method Generator too.
最简单的code里面添加支持组 部分过滤器可
The simplest code which add support of the groups part of Filter can be
public static string Generator (Filter filters) {
var sb = new StringBuilder ();
if (filters.groups != null && filters.groups.Count > 0)
sb.Append (" (");
bool firstRule = true;
if (filters.rules != null) {
foreach (var rule in filters.rules) {
if (!firstRule)
sb.Append (filters.groupOp);
else
firstRule = false;
sb.AppendFormat (FormatMapping[(int)rule.op], rule.field, rule.data);
}
}
if (filters.groups != null && filters.groups.Count > 0) {
sb.Append (") ");
foreach (var filter in filters.groups) {
if (sb.Length != 0)
sb.Append (filter.groupOp);
sb.Append (" (");
sb.Append (Generator (filter));
sb.Append (") ");
}
}
return sb.ToString ();
}
更新时间：：我不得不修改上面的code以产生更复杂的过滤器正确的结果输入：
UPDATED: I have to modify the above code to produce correct results for more sophisticated filters input:
public class Filter {
public GroupOp groupOp { get; set; }
public List<Rule> rules { get; set; }
public List<Filter> groups { get; set; }
}
public class Rule {
public string field { get; set; }
public Operations op { get; set; }
public string data { get; set; }
}
public enum GroupOp {
AND,
OR
}
public enum Operations {
eq, // "equal"
ne, // "not equal"
lt, // "less"
le, // "less or equal"
gt, // "greater"
ge, // "greater or equal"
bw, // "begins with"
bn, // "does not begin with"
//in, // "in"
//ni, // "not in"
ew, // "ends with"
en, // "does not end with"
cn, // "contains"
nc // "does not contain"
}
public static class WhereClauseGenerator {
private static readonly string[] FormatMapping = {
"({0} = '{1}')", // "eq" - equal
"({0} <> {1})", // "ne" - not equal
"({0} < {1})", // "lt" - less than
"({0} <= {1})", // "le" - less than or equal to
"({0} > {1})", // "gt" - greater than
"({0} >= {1})", // "ge" - greater than or equal to
"({0} LIKE '{1}%')", // "bw" - begins with
"({0} NOT LIKE '{1}%')", // "bn" - does not begin with
"({0} LIKE '%{1}')", // "ew" - ends with
"({0} NOT LIKE '%{1}')", // "en" - does not end with
"({0} LIKE '%{1}%')", // "cn" - contains
"({0} NOT LIKE '%{1}%')" // "nc" - does not contain
};
private static StringBuilder ParseRule(ICollection<Rule> rules, GroupOp groupOp) {
if (rules == null || rules.Count == 0)
return null;
var sb = new StringBuilder ();
bool firstRule = true;
foreach (var rule in rules) {
if (!firstRule)
// skip groupOp before the first rule
sb.Append (groupOp);
else
firstRule = false;
sb.AppendFormat (FormatMapping[(int)rule.op], rule.field, rule.data);
}
return sb.Length > 0 ? sb : null;
}
private static void AppendWithBrackets (StringBuilder dest, StringBuilder src) {
if (src == null || src.Length == 0)
return;
if (src.Length > 2 && src[0] != '(' && src[src.Length - 1] != ')') {
dest.Append ('(');
dest.Append (src);
dest.Append (')');
} else {
// verify that no other '(' and ')' exist in the b. so that
// we have no case like src = "(x < 0) OR (y > 0)"
for (int i = 1; i < src.Length - 1; i++) {
if (src[i] == '(' || src[i] == ')') {
dest.Append ('(');
dest.Append (src);
dest.Append (')');
return;
}
}
dest.Append (src);
}
}
private static StringBuilder ParseFilter(ICollection<Filter> groups, GroupOp groupOp) {
if (groups == null || groups.Count == 0)
return null;
var sb = new StringBuilder ();
bool firstGroup = true;
foreach (var group in groups) {
var sbGroup = ParseFilter(group);
if (sbGroup == null || sbGroup.Length == 0)
continue;
if (!firstGroup)
// skip groupOp before the first group
sb.Append (groupOp);
else
firstGroup = false;
sb.EnsureCapacity (sb.Length + sbGroup.Length + 2);
AppendWithBrackets (sb, sbGroup);
}
return sb;
}
public static StringBuilder ParseFilter(Filter filters) {
var parsedRules = ParseRule (filters.rules, filters.groupOp);
var parsedGroups = ParseFilter (filters.groups, filters.groupOp);
if (parsedRules != null && parsedRules.Length > 0) {
if (parsedGroups != null && parsedGroups.Length > 0) {
var groupOpStr = filters.groupOp.ToString();
var sb = new StringBuilder (parsedRules.Length + parsedGroups.Length + groupOpStr.Length + 4);
AppendWithBrackets (sb, parsedRules);
sb.Append (groupOpStr);
AppendWithBrackets (sb, parsedGroups);
return sb;
}
return parsedRules;
}
return parsedGroups;
}
}
现在的你可以使用静态类的静态 ParseFilter 方法 WhereClauseGenerator 像
Now the you can use static ParseFilter method of the static class WhereClauseGenerator like
var filters = request["filters"];
string whereString = request["_search"] && !String.IsNullOrEmpty(filters)
? WhereClauseGenerator.ParseFilter(serializer.Deserialize<Filter>(filters))
: String.Empty;
请不要忘记，与SQL注入问题仍然存在。我不能修复它，直到我不知道你用哪种数据库访问。
Please don't forget that the problem with SQL Injection still exist. I can't fix it till I don't know which kind of database access you use.
这篇关于jqGrid的高级搜索 - 我们能不能使用＆QUOT;和＆QUOT;和＆QUOT;或QUOT;运营商在同一时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
