jquery-Query builder usage

Question

Hi I am new to jquery and I came across a product named "jquery-QueryBuilder" and please find its url below

http://mistic100.github.io/jQuery-QueryBuilder/demo.html

I want to implement that in my asp.net mvc project.can any one help me to implement this or provide some useful url regarding if anyone using this

Control

Answer 1

I've just used this component with MVC. These steps should get you started.

Include query-builder.standalone.min.js in your scripts. NB if you put it in your BundleConfig use Bundle not ScriptBundle as the ScriptBundle Minification seems to cause an error.

bundles.Add(new Bundle("~/bundles/queryBuilder").Include("~/Content/js/query-builder.standalone.min.js"));

Next I created a QueryBuilderSettings class that holds all the settings for the control once it's serialised into JSON

public class QueryBuilderSettings
{
    public List<Filter> filters { get; set; }
    public bool allow_empty { get; set; }
    public int allow_groups { get; set; }
}

public class Filter
{
    public string id { get; set; }
    public string label { get; set; }
    [JsonConverter(typeof(StringEnumConverter))]
    public FilterType? type { get; set; }
    [JsonProperty(ItemConverterType = typeof(StringEnumConverter))]
    public List<FilterOperators> operators { get; set; }
    [JsonConverter(typeof(StringEnumConverter))]
    public InputType? input { get; set; }
    public List<object> values { get; set; }
}

public enum FilterType
{
    @string, 
    @integer, 
    @double, 
    @date, 
    @time, 
    @datetime,
    @boolean
}

public enum FilterOperators
{
    equal,
    not_equal,
    @in,
    not_in,
    less,
    less_or_equal,
    greater,
    greater_or_equal,
    between,
    not_between,
    begins_with,
    not_begins_with,
    contains,
    not_contains,
    ends_with,
    not_ends_with,
    is_empty,
    is_not_empty,
    is_null,
    is_not_null
}

public enum InputType
{
    text,
    textarea,
    radio,
    checkbox,
    select
}

This is a cut down version to give you the idea if you need other settings then add them to this object.

Make a model object that contains a string for the settings and a string for the returned input. In your controller you can create the settings and serialize them into JSON

public ActionResult Index()
{
    QueryScreen query = new QueryScreen();
    QueryBuilderSettings settings = new QueryBuilderSettings();
    settings.allow_empty = true;
    settings.allow_groups = 1;
    settings.filters = new List<Models.Filter>();
    settings.filters.Add(new Models.Filter() { id = "Sku", label = "Sku", type = FilterType.@string, operators = new List<FilterOperators>() { FilterOperators.equal, FilterOperators.not_equal, FilterOperators.begins_with, FilterOperators.not_begins_with } });
    settings.filters.Add(new Models.Filter() { id = "EnglishDesc", label = "English Desc", type = FilterType.@string, operators = new List<FilterOperators>() { FilterOperators.contains, FilterOperators.not_contains } });
    query.QuerySetup = Newtonsoft.Json.JsonConvert.SerializeObject(settings)

    return View(query);
}

Your view now needs to apply the settings to the control

<script>
$(function () {
$(document).ready(function () {
    $('#builder').queryBuilder(@Html.Raw(Model.QuerySetup));
}) 

$("form").submit(function () {
        $('#Query').val(JSON.stringify($('#builder').queryBuilder('getRules', { get_flags: true }), undefined, 2));
        return true;
    })
});
</script>

<div class="col-md-12 col-lg-10 col-lg-offset-1">
    <div id="builder"></div>
    @using (Ajax.BeginForm("GetResults", "Home", new AjaxOptions { InsertionMode = InsertionMode.Replace, HttpMethod = "Post", UpdateTargetId = "results", LoadingElementId = "loading" }, new { @class = "form-inline", role = "form" }))
    {
        @Html.HiddenFor(m => m.Query)
        <button class="btn btn-primary" type="submit">Go</button>
    }
    <div id="results"></div>
    <div id="loading"></div>
</div>

Also on the form submit I'm putting the JSON string into a hiddenFor string on the Model.

Then in the controller you can take this JSON string and parse it into a c# object

[HttpPost]
public PartialViewResult GetResults(QueryScreen screen)
{
    RuleOrGroup query = (RuleOrGroup)Newtonsoft.Json.JsonConvert.DeserializeObject(screen.Query, typeof(RuleOrGroup));
     //do some stuff return a view.
}

public class RuleOrGroup
{
    //Fields if it's a group
    public string condition { get; set; }
    public List<RuleOrGroup> rules { get; set; }

    //Fields if it's a Rule
    public string id { get; set; }
    public string field { get; set; }
    public FilterType type { get; set; }
    public string input { get; set; }
    public FilterOperators @operator { get; set; }
    public string value { get; set; }

    public bool IsAGroup { get { return condition != null; } }
}

Hope that's some use to anyone who stumbles upon this as I couldn't find any examples of using this with MVC out there. Also be kind if it's a poorly constructed answer, it's hard to know how much to put in and what can be cut out.

Cusco

Good part for the filter settings. The most difficult part is using the result, it is a dive into linq expressions (this got me started pretty well: msdn.microsoft.com/en-us/library/mt654267.aspx). Do you have an good idea how to do the linq expression filtering when input type is a radio, select or checkboxes? – Beggs

@CularBytes, sorry once I had my RuleOrGroup object back I translated it into another object that produced SQL, the filter operators for those inputtypes should just be equal though? – Cusco

Answer 2

Here is a simple fiddle.

https://jsfiddle.net/gbegley/k2f46297/

Note the dependencies, which can be a bit of a chore to gather and make available to your users.

<span id="getsql">Get SQL</span>

<br/>
<br/>
<div id="queryBuilderGoesHere"></div>
<div id="sql"></div>
<script>
$("#getsql").addClass("ltblue");

var myFilters = [{
    id: 'column1',
    label: 'Column 1',
    type: 'string'
}, {
    id: 'column2',
    label: 'Column 2',
    type: 'double'
}, {
    id: 'column3',
    label: 'Column 3',
    type: 'boolean'
}];
$("#queryBuilderGoesHere").queryBuilder({
    filters: myFilters
});
$("#getsql").on('click', function () {
    var sqlob = $("#queryBuilderGoesHere").queryBuilder("getSQL", false);
    $("#sql").text(sqlob.sql);
});
</script>

Downright

Answer 3

This is my implementation:

QueryBuilderSettings.cs class

using System;
using System.Diagnostics;
using System.Collections.Generic;
using System.Linq;
using System.Text;

[Serializable]
public class QueryBuilderSettings {  
    public List<QueryBuilderFilter> filters { get; set; }
    public List<string> plugins { get; set; }

    public QueryBuilderSettings()
    {
        this.filters = new List<QueryBuilderFilter>();
        this.plugins = new List<string>();
    }
}

public class QueryBuilderFilter
{
    public string id { get; set; }
    public string label { get; set; }
    public string type { get; set; }
    public List<string> operators { get; set; }
    public string input { get; set; }
    //public List<object> values { get; set; }
    public Dictionary<string, string> values { get; set; }

    public QueryBuilderFilter()
    {

    }

    public QueryBuilderFilter(string id, string label, QueryBuilderDataType type, List<QueryBuilderFilterOperators> ops, QueryBuilderInputType input, Dictionary<string, string> values)
    {
        this.id = id;
        this.label = label;
        this.type = type.ToString();
        this.operators = new List<string>();
        foreach (QueryBuilderFilterOperators op in ops)
        {
            this.operators.Add(op.ToString());
        }
        this.input = input.ToString();
        this.values = values;
    }

    public static QueryBuilderDataType GetQueryBuilderDataType(string PropertyInputType)
    {
        QueryBuilderDataType QBFilterType;

        switch(PropertyInputType)
        {
            case "bool":
                QBFilterType = QueryBuilderDataType.boolean;
                break;
            case "DateTime":
                QBFilterType = QueryBuilderDataType.datetime;
                break;
            case "Date":
                QBFilterType = QueryBuilderDataType.date;
                break;
            case "Time":
                QBFilterType = QueryBuilderDataType.time;
                break;
            case "double":
                QBFilterType = QueryBuilderDataType.@double;
                break;
            case "int":
                QBFilterType = QueryBuilderDataType.integer;
                break;
            case "enum_dropdown":
            case "dropdown":
            case "html":
            case "stringChar":
            case "stringLine":
                QBFilterType = QueryBuilderDataType.@string;
                break;
            default:
                QBFilterType = QueryBuilderDataType.@string;
                break;
        }

        return QBFilterType;
    }

    public static QueryBuilderInputType GetQueryBuilderInputType(string PropertyType)
    {
        QueryBuilderInputType QBInputType;

        switch (PropertyType)
        {
            case "bool":
                QBInputType = QueryBuilderInputType.radio;
                break;

            case "enum":
                QBInputType = QueryBuilderInputType.select;
                break;

            case "DateTime":
            case "double":
            case "int":
            case "string":
                QBInputType = QueryBuilderInputType.text;
                break;
            default:
                QBInputType = QueryBuilderInputType.text;
                break;
        }

        return QBInputType;
    }

    public static List<QueryBuilderFilterOperators> GetQueryBuilderFilterOperator(QueryBuilderInputType? QBInputType )
    {
        List<QueryBuilderFilterOperators> QBFilterOps = new List<QueryBuilderFilterOperators>();
        switch (QBInputType)
        {
            case QueryBuilderInputType.text:
                QBFilterOps = DefaultTextOperators;
                break;
            case QueryBuilderInputType.textarea:
                QBFilterOps = DefaultTextAreaOperators;
                break;
            case QueryBuilderInputType.radio:
                QBFilterOps = DefaultRadioOperators;
                break;
            case QueryBuilderInputType.checkbox:
                QBFilterOps = DefaultCheckBoxOperators;
                break;
            case QueryBuilderInputType.select:
                QBFilterOps = DefaultSelectOperators;
                break;
            case null:
                QBFilterOps = DefaultBlankOperators;
                break;
            default:
                QBFilterOps = DefaultAllOperators;
                break;
        }

        return QBFilterOps;
    }

// Some Default Operators List for convenience

public static List<QueryBuilderFilterOperators> DefaultTextOperators = new List<QueryBuilderFilterOperators>
{
    QueryBuilderFilterOperators.equal,
    QueryBuilderFilterOperators.not_equal,
    QueryBuilderFilterOperators.@in,
    QueryBuilderFilterOperators.not_in,
    QueryBuilderFilterOperators.less,
    QueryBuilderFilterOperators.less_or_equal,
    QueryBuilderFilterOperators.greater,
    QueryBuilderFilterOperators.greater_or_equal,
    QueryBuilderFilterOperators.between,
    QueryBuilderFilterOperators.not_between,
    QueryBuilderFilterOperators.begins_with,
    QueryBuilderFilterOperators.not_begins_with,
    QueryBuilderFilterOperators.contains,
    QueryBuilderFilterOperators.not_contains,
    QueryBuilderFilterOperators.ends_with,
    QueryBuilderFilterOperators.not_ends_with,
    QueryBuilderFilterOperators.is_empty,
    QueryBuilderFilterOperators.is_not_empty,
    QueryBuilderFilterOperators.is_null,
    QueryBuilderFilterOperators.is_not_null
};

    public static List<QueryBuilderFilterOperators> DefaultTextAreaOperators = new List<QueryBuilderFilterOperators>
{
    QueryBuilderFilterOperators.equal,
    QueryBuilderFilterOperators.not_equal,
    QueryBuilderFilterOperators.@in,
    QueryBuilderFilterOperators.not_in,
    QueryBuilderFilterOperators.less,
    QueryBuilderFilterOperators.less_or_equal,
    QueryBuilderFilterOperators.greater,
    QueryBuilderFilterOperators.greater_or_equal,
    QueryBuilderFilterOperators.between,
    QueryBuilderFilterOperators.not_between,
    QueryBuilderFilterOperators.begins_with,
    QueryBuilderFilterOperators.not_begins_with,
    QueryBuilderFilterOperators.contains,
    QueryBuilderFilterOperators.not_contains,
    QueryBuilderFilterOperators.ends_with,
    QueryBuilderFilterOperators.not_ends_with,
    QueryBuilderFilterOperators.is_empty,
    QueryBuilderFilterOperators.is_not_empty,
    QueryBuilderFilterOperators.is_null,
    QueryBuilderFilterOperators.is_not_null
};

public static List<QueryBuilderFilterOperators> DefaultRadioOperators = new List<QueryBuilderFilterOperators>
{
    QueryBuilderFilterOperators.equal,
    QueryBuilderFilterOperators.not_equal,
    QueryBuilderFilterOperators.is_empty,
    QueryBuilderFilterOperators.is_not_empty,
    QueryBuilderFilterOperators.is_null,
    QueryBuilderFilterOperators.is_not_null
};


public static List<QueryBuilderFilterOperators> DefaultCheckBoxOperators = new List<QueryBuilderFilterOperators>
{
    QueryBuilderFilterOperators.@in,
    QueryBuilderFilterOperators.not_in,
    QueryBuilderFilterOperators.is_empty,
    QueryBuilderFilterOperators.is_not_empty,
    QueryBuilderFilterOperators.is_null,
    QueryBuilderFilterOperators.is_not_null
};

    public static List<QueryBuilderFilterOperators> DefaultSelectOperators = new List<QueryBuilderFilterOperators>
{
    QueryBuilderFilterOperators.equal,
    QueryBuilderFilterOperators.not_equal,
    QueryBuilderFilterOperators.is_empty,
    QueryBuilderFilterOperators.is_not_empty,
    QueryBuilderFilterOperators.is_null,
    QueryBuilderFilterOperators.is_not_null
};

    public static List<QueryBuilderFilterOperators> DefaultBlankOperators = new List<QueryBuilderFilterOperators>();

    public static List<QueryBuilderFilterOperators> DefaultAllOperators = new List<QueryBuilderFilterOperators>
{
    QueryBuilderFilterOperators.equal,
    QueryBuilderFilterOperators.not_equal,
    QueryBuilderFilterOperators.@in,
    QueryBuilderFilterOperators.not_in,
    QueryBuilderFilterOperators.less,
    QueryBuilderFilterOperators.less_or_equal,
    QueryBuilderFilterOperators.greater,
    QueryBuilderFilterOperators.greater_or_equal,
    QueryBuilderFilterOperators.between,
    QueryBuilderFilterOperators.not_between,
    QueryBuilderFilterOperators.begins_with,
    QueryBuilderFilterOperators.not_begins_with,
    QueryBuilderFilterOperators.contains,
    QueryBuilderFilterOperators.not_contains,
    QueryBuilderFilterOperators.ends_with,
    QueryBuilderFilterOperators.not_ends_with,
    QueryBuilderFilterOperators.is_empty,
    QueryBuilderFilterOperators.is_not_empty,
    QueryBuilderFilterOperators.is_null,
    QueryBuilderFilterOperators.is_not_null
};
}

//enums

public enum QueryBuilderDataType
{
    @string, 
    @integer, 
    @double, 
    @date, 
    @time, 
    @datetime,
    @boolean
}

public enum QueryBuilderFilterOperators
{
    equal,
    not_equal,
    @in,
    not_in,
    less,
    less_or_equal,
    greater,
    greater_or_equal,
    between,
    not_between,
    begins_with,
    not_begins_with,
    contains,
    not_contains,
    ends_with,
    not_ends_with,
    is_empty,
    is_not_empty,
    is_null,
    is_not_null
}

public enum QueryBuilderInputType
{
    text,
    textarea,
    radio,
    checkbox,
    select
}

WebService

[WebMethod(Description = "Get Query Builder Filter Option")]
    public QueryBuilderSettings GetQueryBuilderFilterOption()
    {
        string QBID;
        string QBLabel;
        string propertyInputType;
        QueryBuilderDataType QBDataType;
        List<QueryBuilderFilterOperators> QBFilterOperators;
        string propertyType;
        QueryBuilderInputType QBInputType;
        //List<object> dropdownValues = new List<object>();
        Dictionary<string, string> dropdownValues = new Dictionary<string, string>();

        QueryBuilderSettings settings = new QueryBuilderSettings();

        // plugins
        settings.plugins.Add("bt-tooltip-errors");
        settings.plugins.Add("not-group");
        settings.plugins.Add("sortable");

        // filters
        List<string> PropertyNames = GetPropertyList(); 

        foreach(string propertyName in PropertyNames)
        {
            QBID = propertyName;
            QBLabel = PropertyTitle[propertyName];
            propertyInputType = GetPropertyInputType(propertyName);
            QBDataType = QueryBuilderFilter.GetQueryBuilderDataType(propertyInputType);
            propertyType = GetPropertyType(propertyName);
            QBInputType = QueryBuilderFilter.GetQueryBuilderInputType(propertyType);
            QBFilterOperators = QueryBuilderFilter.GetQueryBuilderFilterOperator(QBInputType);

            dropdownValues = GetDropdownDictionary(propertyName);

            settings.filters.Add(new QueryBuilderFilter(QBID, QBLabel, QBDataType, QBFilterOperators, QBInputType, dropdownValues));
            //dropdownValues =  new List<object>();
            dropdownValues =  new Dictionary<string, string>(); //Clear the Dictionary or it will add up all dropdown from different properties
        }

        return settings;
    }

Calling it from webpage MyPage.cshtml

<script src="../../Scripts/jQuery.extendext-master/jQuery.extendext.js"></script>
<script src="../../Scripts/jquery.query-builder/js/query-builder.min.js"></script>
<script src="../../Scripts/jquery.query-builder/js/query-builder.standalone.min.js"></script>

<div class="query-builder form-inline">
        <div id="builder"></div>
</div>
<script type="text/javascript">
    $(function () {
        var settings=AjaxFromWebService("../../WS/YourWebService.ASMX/GetQueryBuilderFilterOption", "");

        console.log(settings); // you can see the object when you press F12 on your web browser

        $(document).ready(function () {
            $('#builder').queryBuilder(settings);
        })
    });

    function AjaxFromWebService(Url,JsonData){
        var returnData="";
        $.ajax({
            type: "post",
            contentType: "application/json; charset=utf-8",
            url: Url,
            data: JsonData,
            dataType: "json",
            async:false,
            success: function (result) {
                if(result.d){
                    returnData=result.d;
                } else {
                }
            },
            error: function () {
                BootstrapDialog.alert({message:"Ajax failed",type:BootstrapDialog.TYPE_DANGER});
            }
        });
        return function(){
            return returnData;
        }();
    }
</script>

Rhomb

How to assign validation callback function name? validation : { callback : CustomValidateFunction } – Caban

@Caban maybe you can try this. for each of the "filter" in settings.filters, assign validation as follows. settings.filters[i].validation = { callback: CustomValidateFunction } Or see demo to initialise validation together with your settings querybuilder.js.org/assets/demo-basic.js – Rhomb

I could set it from JS but wanted to know if that could be set from c# at the time of QueryBuilderFilter object creation. – Caban

Not that I know of. Let me know if that's possible! – Rhomb