c# - JQGrid 高级搜索 - 我们可以同时使用 "AND"和 "OR"运算符吗？

Question

我正在使用 JQGrid 高级搜索功能multipleSearch: true, multipleGroup: true。

我也使用 Asp.net MVC 和经典的 ado.net + 存储过程。

每当用户在 JGRID 搜索数据时，我都会将此搜索条件作为参数值传递给存储过程。比如...

Select * 
From tableName 
Where @WhereClauseDynamic

所以我创建了“Where Clause Generator”类。

[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"
}

通过使用上面的代码，当我这样搜索时一切都是正确的

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

所以，这是完全正确的。

但是当涉及到像这样更复杂的搜索查询时......

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

但是我所期望的是下面这样的..

" ((Seminar_Code = 'MED01') AND (Seminar_Code = 'CMP05')) OR ( Seminar_Code = 'CMP01' ) "

那么我怎样才能正确地做到这一点呢？

JQGrid是否支持“AND”+“OR”等多组运算？这个同时只支持一个运算符(operator)吗？我们可以同时使用“AND”和“OR”运算符吗？

每一个建议都会受到赞赏。

Answer 1

首先我应该提到，我发现您使用的代码很危险。您构建要在 SELECT 中使用的 WHERE 结构，并使用信任输入数据。您可能会收到 SQL 注入(inject)问题。您应该更安全地编写代码。您需要对包含 LIKE 的运算符中使用的所有 [、% 和 _ 进行转义。

此外，我建议您使用带参数的 SELECT。而不是

Seminar_Code LIKE 'MED01%'

你可以使用

Seminar_Code LIKE (@p1 + '%')

并使用SqlCommand.Parameters定义 @p1 的值以及您使用的其他参数。

现在我尝试回答您的主要问题。您使用的 Filter 类的定义不使用输入上的 groups 部分。您应该将 Filter 类扩展为类似

public class Filter {
    public GroupOp groupOp { get; set; }
    public List<Rule> rules { get; set; }
    public List<Filter> groups { get; set; }
}

您还应该扩展 WhereClauseGenerator.Generator 方法的代码来分析 groups 部分。我建议您另外使用更接近标准名称转换的名称。如果您对变量使用像 _Filter 这样的名称，而不是对类的私有(private)成员使用，则会导致代码容易被误解。此外，WhereClauseGenerator 类可以是静态的(public static classWhereClauseGenerator)，Generator 方法也可以是静态的。

添加对Filter的groups部分的支持的最简单的代码可以是

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

更新:我必须修改上面的代码才能为更复杂的过滤器输入生成正确的结果:

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

现在您可以使用静态类 WhereClauseGenerator 的静态 ParseFilter 方法，例如

var filters = request["filters"];
string whereString = request["_search"] && !String.IsNullOrEmpty(filters)
    ? WhereClauseGenerator.ParseFilter(serializer.Deserialize<Filter>(filters))
    : String.Empty;

请不要忘记 SQL 注入(inject)的问题仍然存在。在我不知道您使用哪种数据库访问之前我无法修复它。