How to connecting ejGrid to OData and specify a filter whilst maintaining paging and sorting?

[Xander van der Merwe]

I am evaluating Essential Studio for Javascript with a LightSwitch 2013 HTML client using the ejGrid control as a custom control. The grid works well when connecting to any of the LighSwitch OData service entities, but I cannot get filtering working.

Question: How can I create a DataManager and set the url so that it includes an oData filter that will be respected by the ejGrid when it appends it's own parameters to the URL?

For example, the following URL which works correctly by itself, will not work when set here as the ejGrid will automatically append a forward slash and question mark followed by the paging parameters, which then invalidates the URL:

var dm = ej.DataManager({

            url: myapp.rootUri + "/MainData.svc/Orders?$filter=CustomerID eq '" + screen.Customer.CustomerID + "'"

        });

Will generate the following final URL when CustomerID = 'ANTON' which is invalid:

http://localhost:60467/MainData.svc/Orders?$filter=CustomerID%20eq%20%27ANTON%27/?$inlinecount=allpages&$skip=0&$top=12

Any help is appreciated, thanks in advance

Xander

[Madhu Sudhanan P]

Hi Xander,

 

Question: How can I create a DataManager and set the url so that it includes an oData filter that will be respected by the ejGrid when it appends it's own parameters to the URL?

 

var dm = ej.DataManager({

            url: myapp.rootUri + "/MainData.svc/Orders?$filter=CustomerID eq '" + screen.Customer.CustomerID + "'"

        });            

 

When a AJAX request is made, the parameter “$inlinecount” will be added to the url to represent the number of record to be fetched and so giving the filter parameters directly in the url will cause error, to pass the filter parameters in the url use instead the “query” property of the grid. Please refer the following code snippet.

 

 

$("#Grid").ejGrid({

                    dataSource: dm,                   

                    allowPaging:true,

                    allowGrouping: true,

                                              actionBegin:"setQueryValue",

                    . . . . . .

});

 

function setQueryValue(args) {

            args.model.query = ej.Query().where('EmployeeID', 'equal', 3, true);

        }

 

 

By using the above code, the AJAX request to the oData service will be made with the following url.

 

     “http://localhost:60467/MainData.svc/Orders/?$inlinecount=allpages&$skip=0&$top=12&$filter=CustomerID%20eq%20%27ANTON%27”

 

In the above code the query value is set using the actionBegin event.

 

For your convenience we have created a simple sample, please find the attached sample.

 

Please let us know if you require further assistance.

 

Regards,

Madhu Sudhanan. P

Attachment: gridurlbinding_ca7acc8.zip

[Xander van der Merwe]

Thank you, that works great. I just wish there was more documentation for your javascript controls. 

What does the last boolean parameter in the .where() function do, for example?

[Madhu Sudhanan P]

Hi Xander,

 

The “where” function will be used to filter the records based on the filter condition provided as the parameters. Please refer the following table.

 

Name	Parameters	Return Value
Where	fieldName, operator, value, [ignoreCase]	ej.Query
It is used to filter the records based on the filter condition given in the where. Example 1: var first = [{ OrderID: 10248, CustomerID: "VINET", EmployeeID: 2 },              { OrderID: 10249, CustomerID: "AANAR", EmployeeID: 9 }              { OrderID: 10250, CustomerID: "VINET", EmployeeID: 7 }];   var query = ej.Query()               .where("CustomerID", "equal", "vinet”, true);   var dataManager = ej.DataManager(first);   $(function () { // executing query     var result = dataManager.executeLocal(query);   //result = [{ OrderID: 10248, CustomerID: "VINET", EmployeeID: 2 }, { OrderID: 10250, CustomerID: "VINET", EmployeeID: 7 }];   });   Example 2: var query = ej.Query()               .where("CustomerID", "equal", "vinet”, false);   var dataManager = ej.DataManager(first);   $(function () { // executing query     var result = dataManager.executeLocal(query);   //result =[];   });

 

The explanation about the parameters are as follow.

 

filedName – The parameter fieldName indicates the field name of the column to be filtered.

 

operator – The parameter operator is provided with the filter operators. The available filter operator are given as follow

                             For String Values,

                                         -startwith

                                         - endwith

                                         - contains

                                         - equal

                                         -notequal

                            For Integer Values,

               - lessthan

               - greaterthan

               - equal

               - notequal

               - lessthanorequal

               -greaterthanorequal

 

value – The filter value which used to perform filtering.

 

ignorecase – A Boolean parameter when set as true, the filtering is performed without case-sensitive and it is applicable only to String values. It is optional.

 

Please let us know if you require further assistance.

 

Regards,

Madhu Sudhanan. P

[Xander van der Merwe]

Excellent, thank you very much!

[Xander van der Merwe]

I did notice an interesting thing, which I am not sure is by design or possible a bug. 

If you use this query mechanism to filter say an Orders grid based on a CustomerID value and refresh the grid using $(grid).ejGrid("refreshGridContent", true) when that CustomerID changes - say to filter Orders for a different Customer - you will notice that if you use paging in the grid and go to say page 2 (when there are multiple pages of Orders) and then change the CustomerID and refresh the grid, the grid will be empty if the newly selected CustomerID only has 1 grid page of Orders.

The only way I could overcome the above was to reset the paging back to 1 before refreshing the grid like so:

function onCustomerChange() {

        $(grid).ejGrid("sendPagingRequest", 1);

        $(grid).ejGrid("refreshGridContent", true);

}

I would be interested to know whether this is by design or indeed a bug?

Thank you

[Madhu Sudhanan P]

Hi Xander,

 

If the refreshGridContent function is called with the parameter true then it refreshes the whole template of the grid else it refreshes the content of the grid alone.

 

 

$("#Grid").ejGrid("refreshGridContent",true); //Template refresh set as true.

 

$("#Grid").ejGrid("refreshGridContent",false); // Template refresh set as false.

 

$("#Grid").ejGrid("refreshGridContent"); //Template refresh set as false by default.

 

 

 

Case 1:

 

When template refresh is set as false, the content alone will be refreshed and no operations such as paging, filtering, sorting or grouping are considered.

 

Case 2:

 

When template refresh is set as true, the whole grid template will get refreshed which means the filtering , sorting , and grouping are cleared and the current grid page is set to 1.

 

Since there is a bug, the refreshGridContent with template refresh set as true, is not working as we explained above. The fix for this issue will be available in our 2014 Volume 1 on the month of April.

 

Please let us know if you require further assistance.

 

Regards,

Madhu Sudhanan. P                                               

[Xander van der Merwe]

Thanks again for the clear explanation, that is great.

[Maithiliy K]

Hi Xander,

 

Thanks for your appreciation.

 

Please get back to us if you have any queries. We will be happy to assist you.

 

Regards,

Maithiliy K