JQGrid Advance Search - Could we use “AND” and “OR

Question

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 stored procedure as parameter values. Such as ...

Select * 
From tableName 
Where @WhereClauseDynamic

So I have created "Where Clause Generator" Class.

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

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 correctly?

Is JQGrid support multiple group operation like "AND" + "OR" ? Is this support only one operator at the same time? Could we use "AND" and "OR" opreators at the same time ?

Every suggestion will be appreciated.

Answer 1

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.

Moreover I would recommend you to use SELECT with parameters. Instead of

Seminar_Code LIKE 'MED01%'

you can use

Seminar_Code LIKE (@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; }
}

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.

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

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

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;

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.