[Solved] Grid Bound to Remote XML w/ Server Paging/Sorting/Filtering

[Ashleigh L]

We're looking to convert our existing grids to KendoUI grids and I'm working on a proof of concept example. We need to be able to support grids that have data provided by a function that returns XML, and that have server side paging, sorting and filtering. I've already got the code up and running to create the grid using the XML returned from my function, but I'm having some trouble locating resources on how to set up the server paging/sorting/filtering.

Here's my grid setup code:

01.var xmlDataRemote = new kendo.data.DataSource({
02.                serverPaging: true,
03.                serverFiltering: true,
04.                serverSorting: true,
05.                pageSize: 20,
06.                transport: {
07.                    read: { url: "/Controller.cfc?method=getData }
08.                },
09.                schema: {
10.                    type: "xml",
11.                    data: "/Items/Item",
12.                    total: ???,
13.                    model: {
14.                        id: "ID",
15.                        fields: {
16.                            Name: "Name/text()",
17.                            Status: "Status/text()",
18.                            Type: "Type/text()"
19.                        }
20.                    }
21.                }
22.            });
23.
24.            $("#grid").kendoGrid({
25.                dataSource: xmlDataRemote,
26.                pageable: true,
27.                columns: [
28.                    { title: "Name", field: "Name" },
29.                    { title: "Status", field: "Status" },
30.                    { title: "Type", field: "Type" }
31.                ]
32.            });

Here's some example XML:

<Items>
    <Item>
        <ID>1</ID>
        <Name>First Item Name</Name>
        <Status>Active</Status>
        <Type>Online</Type>
    </Item>
    <Item>
        <ID>2</ID>
        <Name>Second Item Name</Name>
        <Status>Inactive</Status>
        <Type>External</Type>
    </Item>
    <TotalRows>22</TotalRows>
</Items>

As I mentioned, we already use another library for grids that support server-side paging/filtering/sorting, and our backend is Coldfusion. Here's an example of an existing function to get XML data for a grid:

001.<cffunction name="getData" access="remote" returnType="xml" output="true">
002.    <cfargument name="pagenum" required="no" type="numeric" default=0 />
003.    <cfargument name="pagesize" required="no" type="numeric" default=20 />
004.    <cfargument name="sortdatafield" required="no" type="string" default =""/>
005.    <cfargument name="sortorder" required="no" type="string" default ="asc" />
006.    <cfargument name="filterscount" required="no" type="numeric" default ="0" />
007.
008.    <!--- paging information --->
009.    <cfset start = (((ARGUMENTS.PageNum + 1) * ARGUMENTS.PageSize) - ARGUMENTS.PageSize + 1)/>
010.    <cfset end = (ARGUMENTS.PageNum + 1) * ARGUMENTS.PageSize/>
011.
012.    <cfquery datasource="#APPLICATION.DSN#" name="get_items">
013.        WITH items AS (
014.            SELECT      TOP 100 PERCENT row_number() OVER (
015.                            ORDER BY
016.                                <cfswitch expression="#ARGUMENTS.sortdatafield#">
017.                                    <cfcase value="Name">Name #ARGUMENTS.sortorder#</cfcase>
018.                                    <cfcase value="Status">Status #ARGUMENTS.sortorder#</cfcase>
019.                                    <cfcase value="Type">Type #ARGUMENTS.sortorder#</cfcase>
020.                                    <cfdefaultcase>Name ASC</cfdefaultcase>
021.                                </cfswitch>
022.                            ) as row,
023.                            count(*) over() as 'Total_Rows',
024.                        Name, Status, Type
025.            FROM
026.            (SELECT         I.Name, I.Status, I.Type
027.            FROM            tbl_items as I
028.            WHERE           Account_ID = 1234
029.            <!--- filter sql --->
030.            <cfif ARGUMENTS.filterscount GT 0>
031.                <cfloop from='1' to='#ARGUMENTS.filterscount#' index="filterIndex">
032.                    <cfset filterDataField = ARGUMENTS['filterdatafield' & (filterIndex - 1)] />
033.                    <cfset filterCondition = ARGUMENTS['filtercondition' & (filterIndex - 1)] />
034.                    <cfset filterValue = ARGUMENTS['filtervalue' & (filterIndex - 1)] />
035.
036.                    <cfif ARGUMENTS['filterdatafield' & (filterIndex - 2)] NEQ filterDataField or filterIndex EQ 1>
037.                        AND (
038.                    <cfelse>
039.                        <cfif ARGUMENTS['filteroperator' & (filterIndex -1)] EQ 0> AND <cfelse> OR </cfif>
040.                    </cfif>
041.
042.                    <cfswitch expression="#filterDataField#">
043.                        <cfcase value="Name">I.Name</cfcase>
044.                    </cfswitch>
045.
046.                    <cfswitch expression="#filterCondition#">
047.                        <cfcase value="CONTAINS"> LIKE <cfqueryparam value="%#filterValue#%" /></cfcase>
048.                        <cfcase value="CONTAINS_CASE_SENSITIVE"> COLLATE Latin1_General_CS_AS LIKE <cfqueryparam value="%#filterValue#%" /></cfcase>
049.                        <cfcase value="DOES_NOT_CONTAIN"> NOT LIKE <cfqueryparam value="%#filterValue#%" /></cfcase>
050.                        <cfcase value="DOES_NOT_CONTAIN_CASE_SENSITIVE"> COLLATE Latin1_General_CS_AS NOT LIKE <cfqueryparam value="%#filterValue#%" /></cfcase>
051.                        <cfcase value="EQUAL"> = <cfqueryparam value="#filterValue#" /></cfcase>
052.                        <cfcase value="EQUAL_CASE_SENSITIVE"> COLLATE Latin1_General_CS_AS = <cfqueryparam value="#filterValue#" /></cfcase>
053.                        <cfcase value="NOT_EQUAL"> <> <cfqueryparam value="#filterValue#" /></cfcase>
054.                        <cfcase value="GREATER_THAN"> > <cfqueryparam value="#filterValue#" /></cfcase>
055.                        <cfcase value="LESS_THAN"> < <cfqueryparam value="#filterValue#" /></cfcase>
056.                        <cfcase value="GREATER_THAN_OR_EQUAL"> >= <cfqueryparam value="#filterValue#" /></cfcase>
057.                        <cfcase value="LESS_THAN_OR_EQUAL"> <= <cfqueryparam value="#filterValue#" /></cfcase>
058.                        <cfcase value="STARTS_WITH"> LIKE <cfqueryparam value="#filterValue#%" /></cfcase>
059.                        <cfcase value="STARTS_WITH_CASE_SENSITIVE"> COLLATE Latin1_General_CS_AS LIKE <cfqueryparam value="#filterValue#%" /></cfcase>
060.                        <cfcase value="ENDS_WITH"> LIKE <cfqueryparam value="%#filterValue#" /></cfcase>
061.                        <cfcase value="ENDS_WITH_CASE_SENSITIVE"> COLLATE Latin1_General_CS_AS LIKE <cfqueryparam value="%#filterValue#" /></cfcase>
062.                        <cfcase value="NULL"> IS NULL </cfcase>
063.                        <cfcase value="NOT_NULL"> IS NOT NULL </cfcase>
064.                        <cfcase value="EMPTY"> = '' </cfcase>
065.                        <cfcase value="NOT_EMPTY"> <> '' </cfcase>
066.                    </cfswitch>
067.
068.                    <cfif ARGUMENTS['filterdatafield' & (filterIndex)] NEQ filterDataField or filterIndex EQ ARGUMENTS.filterscount>
069.                        )
070.                    </cfif>
071.                </cfloop>
072.            </cfif>) as innerselect
073.            <!--- order by sql --->
074.            ORDER BY
075.                <cfswitch expression="#ARGUMENTS.sortdatafield#">
076.                    <cfcase value="Name">Name #ARGUMENTS.sortorder#</cfcase>
077.                    <cfcase value="Status">Status #ARGUMENTS.sortorder#</cfcase>
078.                    <cfcase value="Type">Type #ARGUMENTS.sortorder#</cfcase>
079.                    <cfdefaultcase>Name ASC</cfdefaultcase>
080.                </cfswitch>
081.            )
082.            <!--- paging sql --->
083.            SELECT *
084.            FROM items
085.            WHERE row BETWEEN <cfqueryparam cfsqltype="cf_sql_integer" value="#start#" /> AND <cfqueryparam cfsqltype="cf_sql_integer" value="#end#" />
086.            ORDER BY row ASC
087.    </cfquery>
088.
089.    <cfxml variable="return">
090.        <cfif get_items.RecordCount EQ 0>
091.            <Items>
092.                <Item />
093.                <TotalRows>0</TotalRows>
094.            </Items>
095.        <cfelse>
096.            <Items>
097.                <cfoutput query="get_items">
098.                    <Item>
099.                        <ID>#get_items.ID#</ID>
100.                        <Name>#get_items.Name#</Name>
101.                        <Status>get_items.Status)#</Status>
102.                        <Type>#get_items.Type#</Type>
103.                    </Item>
104.                </cfoutput>
105.                <TotalRows>#Total_Rows#</TotalRows>
106.            </Items>
107.        </cfif>
108.    </cfxml>
109.
110.    <cfreturn return />
111.</cffunction>

As you can see, we pass in 5 arguments which control the page number, page size, which column/field to sort and in what order, and filter information.

For the Kendo grid, I know I need to set the total for the datasource schema, but I'm not sure how to get that value out of my XML. I'm also not sure what information I need to pass into my function that will tell it which page to get next or how to sort/filter the data. I appreciate any help you can provide.

[Ashleigh L]

So after getting the basic set up complete, I can see you're passing the paging/sorting/filtering data via URL params, similar to our old system. However, I'm still having difficulties getting the total set up in my datasource, and I'm not sure how to make Coldfusion function accept your sorting/filtering parameters, since they aren't encoding properly:

/development/KendoDashboard/KendoController.cfc?method=getGrid&take=20&skip=0&page=1&pageSize=20&sort%5B0%5D%5Bfield%5D=Name&sort%5B0%5D%5Bdir%5D=desc&filter%5Blogic%5D=and&filter%5Bfilters%5D%5B0%5D%5Bfield%5D=Name&filter%5Bfilters%5D%5B0%5D%5Boperator%5D=eq&filter%5Bfilters%5D%5B0%5D%5Bvalue%5D=test

In comparison, here's a call using our current grids:

/MainController.cfc?method=getData&Nameoperator=and&filtervalue0=test&filtercondition0=CONTAINS&filteroperator0=0&filterdatafield0=Name&filterscount=1&pagenum=0&pagesize=20&recordstartindex=0&recordendindex=20&_=1419007520579

I'm also not sure what the take and skip parameters are meant for, some clarification or a link to a help would be appreciated.

[Atanas Korchev]

Hi,

You can customise the way the data source parameters are passed via the parameterMap function.

Total can be set as a function:

schema: {
   total: function (response) {
     // calculate total and return it

    return 100;
   }
}

Regards,
Atanas Korchev
Telerik

 

 

[Ashleigh L]

As noted in my examples, I'm already returning the total number of records as a XML node, why do I need a function to calculate it? I've tried setting it similarly to the data or fields sections, but neither works:

var xmlDataRemote = new kendo.data.DataSource({
    transport: {
        read: { url: "/user/styles/development/KendoDashboard/KendoController.cfc?method=getGrid" },
        parameterMap: function(data, type) {
            return "Parameters=" + kendo.stringify(data);
        }
    },
    pageSize: 20,
    serverPaging: true,
    serverSorting: true,
    serverFiltering: true,
    schema: {
        type: "xml",
        data: "/Courses/Course",
        total: "/Courses/TotalRows",
        model: {
            id: "ID",
            fields: {
                Name: { field: "Name/text()", type: "string" },
                Status: { field: "Status/text()", type: "string" },
                Type: { field: "Type/text()", type: "string" }
            }
        }
    }
});

or

var xmlDataRemote = new kendo.data.DataSource({
    transport: {
        read: { url: "/user/styles/development/KendoDashboard/KendoController.cfc?method=getGrid" },
        parameterMap: function(data, type) {
            return "Parameters=" + kendo.stringify(data);
        }
    },
    pageSize: 20,
    serverPaging: true,
    serverSorting: true,
    serverFiltering: true,
    schema: {
        type: "xml",
        data: "/Courses/Course",
        total: "TotalRows/text()",
        model: {
            id: "ID",
            fields: {
                Name: { field: "Name/text()", type: "string" },
                Status: { field: "Status/text()", type: "string" },
                Type: { field: "Type/text()", type: "string" }
            }
        }
    }
});

[Atanas Korchev]

Hi,

Here is a demo which shows how to specify the total: http://dojo.telerik.com/@korchev/Eyaqo
Regards,
Atanas Korchev
Telerik