Search And Or

Dec 13, 2011 at 8:24 PM

First, thanks for a the great work and very nice library.

My question is about advanced search. You can add conditions with AND / OR. However if you add first two conditions with OR, and finally adds a third condition with AND, then the whole query is changed to AND.

For example if you have "Id = 1 OR Id = 2" then you want to add "AND Price = 20" at the end. Shouldn't it then result in "(Id = 1 OR Id = 2) AND Price = 20"? But instead the result will look like this "Id = 1 AND Id = 2 AND Price = 20".

So I'm wondering if it's possible to remove this AND / OR option and just use OR for every condition to avoid confused users.

Thanks, remda

Coordinator
Dec 13, 2011 at 9:32 PM

Well I believe you have misunderstood the purposes of this dropdown - it is used do define a single operator which is connecting all the rules. The reason for this confusion is how the options are currently named in jqGrid, they used to be named "All" and "Any", which was describing the purpose of the drilldown a lot better.

To answer your question, there is no option in jqGrid to hide this dropdown for advanced searching. But you can enabled multiple groups in advanced searching (AdvancedSearchingWithGroups option in helper) which allows for creating nested groups - that way user can create a filter expression which will look like the one you have described.

Jul 9, 2013 at 1:32 PM
Edited Jul 9, 2013 at 1:34 PM
Tomasz (or anyone),

I was able to turn on the advanced searching using the multipleGroup:true property. But I haven't been able to find anything on how to use the AdvancedSearchingWithGroups option in the helper.

Currently, with just the multipeGroup option turned on, I can build a combined And/Or filter but when I click the Find button, it's only returning me just the outer AND filter clause in the request filters. So I'm assuming that's why I need to set the AdvancedSearchingWithGroups option but can you give me an example of where I need to set that.

Thank you for the great control.

Mike
Coordinator
Jul 9, 2013 at 8:08 PM
Hi Mike,

In order to enable the AdvancedSearchingWithGroups in the helper, you just need to set the corresponding option to true:
.Navigator(new Lib.Web.Mvc.JQuery.JqGrid.JqGridNavigatorOptions() { ... },
    searchActionOptions: new Lib.Web.Mvc.JQuery.JqGrid.JqGridNavigatorSearchActionOptions() { AdvancedSearching = true, AdvancedSearchingWithGroups = true, ... });
This should result in search dialog with capabilities like in the below screenshot:

AdvancedSearchingWithGroups

The tricky part is the proper request handling on the server side (as it is not part of helper responsibility). If you have looked through the sample project you most probably have seen below code in Products action method:
else if (request.SearchingFilters != null)
{
    StringBuilder filterExpressionBuilder = new StringBuilder();
    string groupingOperatorToString = request.SearchingFilters.GroupingOperator.ToString();
    foreach (JqGridRequestSearchingFilter searchingFilter in request.SearchingFilters.Filters)
    {
        filterExpressionBuilder.Append(GetFilter(searchingFilter.SearchingName, searchingFilter.SearchingOperator, searchingFilter.SearchingValue));
        filterExpressionBuilder.Append(String.Format(" {0} ", groupingOperatorToString));
    }
    if (filterExpressionBuilder.Length > 0)
        filterExpressionBuilder.Remove(filterExpressionBuilder.Length - groupingOperatorToString.Length - 2, groupingOperatorToString.Length + 2);
    filterExpression = filterExpressionBuilder.ToString();
}
This code is only handling request.SearchingFilters.Filters collection which describeds only the highest level of filters. In order to handle the lower levels you need to recursivly iterate through request.SearchingFilters.Groups collection.
Jul 9, 2013 at 8:47 PM
Thanks for the information. I believe I can do the controller side once I get this working but I'm not being able to figure out how to still use your helper. I couldn't find anywhere in your sample project that your doing anything like that so not sure how to apply it to my existing code.

I'm defining my search as follows (basically like your sample program):
            $('#jqgTransmissions').jqGrid('navGrid', '#jqgpTransmissions', { edit: false, add: false, del: false, search: false }).jqGrid('navButtonAdd', '#jqgpTransmissions', {
                caption: 'Advanced Search',
                buttonicon: 'ui-icon-search',
                onClickButton: function() {
                    $('#jqgTransmissions').jqGrid('searchGrid', { multipleSearch: true, multipleGroup:true, width: 600 });    //multipleGroup:true, 
                },
                position: 'last',
                title: 'Search'
            }).jqGrid('navButtonAdd', '#jqgpTransmissions', {  {at this point I'm adding some additional custom buttons}
So not sure where your .Navigator should go. (Sorry, I'm still pretty new with Javascript and especially with jqGrid). I was going to post the my entire grid script file but it was too long to post so I'm just posting the main part of the grid below. So let me know if you might actually need the entire grid code to see what I have if that would help more. And I wouldn't mind any constructive comments on anything that I could be doing better with it since I've just been basically finding other examples and putting them into it which so far has been doing exactly what I've been wanting it to do but probably could be doing it better.

The grid layout I am using is basically following the same layouts you have in your example program similar to:
    <script type="text/javascript">
        $(document).ready(function () {
            $('#jqgTransmissions').jqGrid({
                url: '@Url.Action("TransmissionLogs")',     //url from wich data should be requested
                datatype: 'json',                           //type of data
                mtype: 'POST',                              //url access method type
                colNames: ['Action', 'ID', 'Entity', 'File Name', 'File Status', 'Received', 'Source', 'Sent', 'Destination'],     //columns names
                colModel: [ column layout here]
                pager: $('#jqgpTransmissions'),     //pager for grid
                rowNum: 10,                         //number of rows per page
                sortname: 'EntityID',               //initial sorting column
                sortorder: 'asc',                   //initial sorting direction
                viewrecords: true,                  //we want to display total records count
                height: '100%',                     //grid height
                caption: "Advantage Transmission Logs",
                autowidth: true,
                multiselect: true,
                rowList: [10, 20, 30, 40, 50, 75, 100],
                gridComplete: function() { action buttons defined here}
                    }
                },
            });
            $('#jqgTransmissions').jqGrid('filterToolbar', {
                //we want filters to be returned the same way as with advanced searching
                stringResult: true
            });
           {here is where the first section of code above goes}
Coordinator
Jul 9, 2013 at 9:10 PM
So you are using only the server side part of the helper based on samples from jqGrid in ASP.NET MVC 3 and Razor not from jqGrid in ASP.NET MVC - Strongly typed helper right? In that case the correct sample to look at is AdvancedSearching.cshtml and you should modify the call to native navGrid method of jqGrid:
$('#jqgProducts').jqGrid('navGrid', '#jqgpProducts', { ... }, { }, { }, { }, {
    multipleSearch: true,
    multipleGroup: true,
    ...
});
Which in general is the same as your code (you do enable the feature we are talking about here) but doesn't require custom button by just using native searching of navGrid instead.
Jul 10, 2013 at 3:55 PM
Edited Jul 10, 2013 at 6:31 PM
Thank you, I didn't realize I wasn't using your sample program for the Strongly typed helper. I changed it as above to use the native navGrid now but I'm still not seeing any other searching (group) values in the request (JqGridRequest) object being passed to the Post function. All I'm seeing in the request object is:

PageIndex = 0
PagesCount = null
RecordsCount = 10
Searching = true
SearchingFilter = null {Lib.Web.Mvc.JQuery.JqGrid.JqGridRequestSearchingFilter}
SearchingFilters = {Lib.Web.Mvc.JQuery.JqGrid.JqGridRequestSearchingFilters} {has just one filter in it}
SortingName "EntityID"
SortingOrder = Lib.Web.Mvc.JQuery.JqGrid.JqGridSortingOrders.Asc

I'm going to look at that to see what it looks like to see if that would be a better option for me but would probably be more work to change my exiting program over to it, so would be great if I can just get this one last functionality to work.

Thanks again and I appreciate the time your taking to help me out.

{I was able to figure out to get the Groups information by using a 'string filters' parameter on my POST function. So I can at least get them that way unless you have a better easier way than deserializing the filters parameter}
Coordinator
Jul 10, 2013 at 8:35 PM
Please check what exactly is stored in SearchingFilters, if you are not using some very old version of the library (there was a bug in some older versions) everything should be there. For example in case of search dialog set up like in the screenschot I have attached earlier the content should be as follow:

SearchingFilters.Filters <-- this should contain only one item, the filter for Name field
SearchingFilters.Groups <-- this should contain two items, representing the nested groups (each of those groups should contain two filters)

Are you sure that groups are not there? Which version of Lib.Web.Mvc are you using?
Jul 11, 2013 at 2:08 PM
OK, that was the problem. I had updated the sample program with the latest NuGet package of the library so it was on 6.2.1. But somewhere among my testing and attempts to get it working, I must have reverted back to a project that was still using the original library file in it (3.3.0). I updated my current working project back to 6.2.1 again and now I'm getting the Groups within the SearchingFilters. Looks like everything is working great for me now!

I appreciate your time and hopefully will stay quite for awhile. I might like to play with your Alpha version to see if I can get the row color to work so I might end up asking some basic questions on that back in your blog for it, but I'm still waiting if I really need to do the full row color yet.

Thanks again.
Mike