This article will cover how we implemented server-side logic to create a virtualized Kendo Grid using ColdFusion. This is a highly technical article and it is assumed that you have some familiarity with server-side languages, such as ColdFusion. My goal is to allow other developers either reverse engineer this code using other languages, such as PHP, and to allow other ColdFusion developers to understand or customize this code. You should also read part 1 of this article, Implementing a Kendo Virtualized Grid with ColdFusion, in order to understand how this is being consumed by the client.  

Table of Contents

Download ColdFusion Templates from GitHub

I have created four different files on GitHub to use to create Kendo virtualized grids. You should download these files in order to follow along.

  1. The first file, containing the getWorldDbForKendoGrid method that we are using here, can either be copied below or downloaded at https://github.com/GregoryAlexander77/KendoUtilities/blob/main/KendoVirtualGridServiceEndpoint. This file needs some minor modifications that we will cover below.
  2. You also need ou CfJson component if you don't already have it. This ColdFusion component is used for almost every Kendo widget that consumes JSON data using a ColdFusion service endpoint. This ColdFusion component is found at https://github.com/GregoryAlexander77/CfJson.
  3. The first function (getWorldDbForKendoGrid) relies upon the KendoUtils.cfc component containing the core logic to prepare the SQL statements based on the JSON arguments sent by the Kendo grid.
  4. Finally, the ColumnProperty.cfc component is used by the function to determine the column datatype. 

Analyzing the Logic Used as the Service Endpoint

The getWorldDbForKendoGrid ColdFusion file is used to create the Kendo Service Endpoint. This file is consumed by the client, via Ajax, whenever the grid needs to be rendered with either initial or new data. 

To best illustrate how the client invokes this endpoint, open the example grid below and open up the browser debugging window. Once the window is open, the debugging window can typically be invoked by clicking on the f12 key if you're using Chrome. All of the browsers have their own debugging features, and I will try to blog about this in a future post. I also will include pictures of the debugger below so it is not necessary to use the debugger to inspect the traffic yourself.

Inspecting the Kendo UI Ajax Payload

Kendo Virtual Method Arguments

Kendo sends various arguments to our ColdFusion endpoint. The default data transport parameters sent to our ColdFusion endpoint via AJAX are:

  • take and pageSize (numeric)
    The take paramater is not documented on the Kendo datasource methods site. However, it is identical to the pageSize parameter. Both of these parameters specify the number of items that should be returned in the data object. These parameters are only available when the serverPager argument is set to true and the Kendo datasource makes a read request.
  • skip (numeric)
    The number of items that should be skipped when a new page is formed.
  • page (numeric)
    Determines the page number. This value is displayed at the bottom of the grid when a pager is used.
  • sort (array)
    Optional argument that determines the data sort condition. 
  • filter (array)
    This is an optional parameter that sends an array of objects to determine the filters used on the data. 

We will explain each of these arguments in greater detail below.

Introducing the Kendo Filter Objects

The filter object will be also passed by the Kendo data source as a payload to our service endpoint and will be used to construct our SQL statements to populate the virtual grids. The filter objects used for virtual grids are:

  • filter.field
    The field is the database column that we are applying filters to, such as the Country, City, or Latitude in our examples.
  • filter.logic
    used to specify a logical condition. Values are:
    • and
    • or
  • filter.operator
    Various operators for comparison, such as equal or not equal. See filter.operator for more information.
  • filter.value
    The value specified by the user. In our examples, the values are the city or country, such as 'Seattle' or 'United States', or the latitude that was typed in to search for cities with the same latitude as Los Angeles.

Kendo Virtual Datasource Payload Examples

The Payload When the Grid is Initially Rendered

The first example below shows the default payload sent by the Kendo Datasource when the grid is first rendered. This is the easiest to understand. These parameters are:

  • page: 1, which is the first page of the grid that is displayed in the pager at the bottom of the grid.
  • pageSize: 100, the number of rows within the virtual grid.
  • skip: 0 we are not skipping any of the records and starting from the first record in the data object
  • take: 100 we are retrieving the first hundred records.
  

Payload Sent by the Kendo Datasource Past 100 Records 

The Kendo Datasource automatically sends the following parameters to the endpoint when either the user scrolls beyond 100 records or when the user clicks on the 2nd page using the pager control at the bottom of the page. 

  • page: 2, page 2 is displayed in the pager at the bottom of the grid.
  • pageSize: 100, the number of rows within the virtual grid. 
  • skip: 100 we are skipping the first 100 records in the data object
  • take: 100 we are retrieving the first hundred records.

Payload When a Filter is Made, the City column equals Seattle

If the user filters the data, as in this case when the user wants to filter the city column where the city is Seattle, the following parameters are sent to the endpoint by the Kendo Datasource:

  • page: 1, page 1 is displayed in the pager at the bottom of the grid.
  • pageSize: 100, the number of rows within the virtual grid. 
  • skip: 0 we are skipping the first 100 records in the data object
  • take: 100 we are retrieving the first hundred records.
  • filters: [{field: "City", operator: "eq", value: "Seattle"}] logic: "and"
    The filters parameter sends an array of arguments to determine what records should be retrieved by the endpoint. Here, City is the column that we are filtering, and the user chose 'show items that value is' 'equal to' 'Seattle'. We will go over this in finer detail later in this article.

Another Filter Payload Example, Country equals United States and Sorting by the Country

If the user filters the data, as in this case when the user wants to filter the city column where the country column is the United States, the following parameters are sent to the endpoint by the Kendo Datasource:

  • page: 1, page 1 is displayed in the pager at the bottom of the grid.
  • pageSize: 100, the number of rows within the virtual grid. 
  • skip: 0 we are skipping the first 100 records in the data object
  • take: 100 we are retrieving the first hundred records.
  • filters: [{field: "Country", operator: "eq", value: "United States"}] logic: "and"
    The filters parameter sends an array of arguments to determine what records should be retrieved by the endpoint. Here, Country is the column that we are filtering, and the user chose 'show items that value is' 'equal to' 'United States'. 
  • sort: [{field: "Country", dir: "asc"}] Here we are also sorting the country column in ascending order. Again, we will explain the logic further later in the article.

Finding Major Cities that Share the Same Latitude as Los Angeles

In this example, we are finding the major cities that share the same latitude as Los Angeles. We are searching for latitudes greater than or equal to 32.02 and less than 32.08. Some of the major cities that share the same latitude as LA are Raleigh North Carolina and Tel Aviv Israel.

  • page: 1, page 1 is displayed in the pager at the bottom of the grid.
  • pageSize: 100, the number of rows within the virtual grid. 
  • skip: 0 we are skipping the first 100 records in the data object
  • take: 100 we are retrieving the first hundred records.
  • filter: {logic: "and", filters: {field: "CityLatitude", operator: "gte", value: 32.02},{field: "CityLatitude", operator: "lte", value: 32.07}
    The filters parameter sends an array of arguments to determine what records should be retrieved by the endpoint. Here, CityLatitude is the column that we are filtering, and the user chose 'show items that value is' 'greater than or equal to' '32.02' and less than or equal to 32.07. 

Now that we have a better understanding of how the Kendo Datasource sends parameters for a virtualized grid, we will analyze the logic for the service endpoint.

Analyzing the getWorldDbForKendoGrid Service Endpoint

In our companion Kendo virtual grid article, Implementing a Kendo Virtualized Grid with ColdFusion, we learned how to incorporate a Kendo virtualized grid using the getWorldDbForKendoGrid method as a ColdFusion Service Endpoint. Here, we will cover the server-side logic used in this endpoint. We have provided a copy of this component on GitHub. You may want to open this GitHub file in order to follow along. 

Preset Parameters

At the top of the file, we are presetting the parameter values sent by the Kendo data source that we just covered, along with setting the default values for other ColdFusion variables. 

<!--- Set params --->
<cfparam name="take" default="100" type="string">
<cfparam name="skip" default="0" type="string">
<cfparam name="page" default="1" type="string">
<cfparam name="pageSize" default="100" type="string">
<cfparam name="whereClause" default="" type="string">
<cfparam name="sortStatement" default="" type="string">
<cfparam name="searchFilter" default="false" type="boolean">
<cfparam name="logSql" default="true" type="boolean">

User Defined Variables

The following logic should be set when creating the method to act as the service endpoint. We have covered these user-defined settings in our previous article. The tableName, tableNameAlias, primaryKey, defaultOrderByStatement, and sqlStatement variables should be self-explanatory. Simply put, we need to set the default values for the SQL queries. The getTotal query simply returns the number of records found in the query and should not have to change if you're using SQL Server. You may have to change this logic if using a different database.

<!--- The name of the view (or a table that is derived from a view. --->
<cfset tableName = 'ViewCountryStateCity'>
<cfset tableNameAlias = 'vCountryStateCity'>
<cfset primaryKey = "CityId">
<cfset defaultOrderByStatement = 'ORDER BY Country'>

<!--- Get the number of records in the entire table, not just the top 100 for display purposes. We will overwrite this later if there are any new filters applied.  --->
<cfquery name="getTotal" datasource="#dsn#">
	SELECT count(#primaryKey#) as numRecords FROM [dbo].[#tableName#]
</cfquery> 
<cfset totalNumRecords = getTotal.numRecords>

<!--- Make the query. Input the select statement *without* the from clause (or any other clauses) here. --->
<cfset sqlStatement = '
	SELECT * FROM
		( 	SELECT
			CountryId
			,Country
			,Capital
			,Currency
			,CurrencyName
			,ISO2
			,ISO3
			,Flag
			,Latitude
			,Longitude
			,StateId
			,State
			,StateFlag
			,Type
			,CityId
			,City
			,CityLatitude
			,CityLongitude
			,CityFlag
'>

As we have stated in our previous article, you should not have to define any of the variables past this point, however, we will try to explain the core logic of the code in order to revise it if using a database other than SQL Server, or to reverse engineer the code for another server-side language.

Logic to Capture the Kendo DataSource Ajax Virtual Grid Parameters

The following logic captures the parameter data sent via the Kendo DataSource. We are using getHttpRequestData().content ColdFusion function to capture the JSON data from the header. This particular technique was taken from Ben Nadel's Posting JSON Data To The ColdFusion Server Using jQuery article which is also on GitHub.

After capturing the JSON, we simply test to see if the requestBody is a JSON string and we then deserialize the JSON and pass the JSON to the createSqlForVirtualGrid method found in the KendoUtils component. This function is quite large as it contains all of the logic necessary to use the data from the Kendo Datasource to construct the SQL statement that we will use to query the database. 

<!---
        Get the HTTP request body content.
        The content in the request body should be formatted like so: {"take":100,"skip":9300,"page":94,"pageSize":100,"sort":[{"field":"ref2","dir":"desc"}]}
        
        NOTE: We have to use toString() as an intermediary method
        call since the JSON packet comes across as a byte array
        (binary data) which needs to be turned back into a string before
        ColdFusion can parse it as a JSON value. 
        See https://www.bennadel.com/blog/2207-posting-json-data-to-the-coldfusion-server-using-jquery.htm for
        more information.
  --->
<cfset requestBody = toString( getHttpRequestData().content ) />

<cfif isJSON( requestBody )>
	<!--- Deserialize the json in the request body.  --->
	<cfset incomingJson = deserializeJSON( requestBody )>

        <cfinvoke component="#this#" method="createSqlForVirtualGrid" returnvariable="sqlStruct">
            <cfinvokeargument name="jsonString" value="#requestBody#">
            <cfinvokeargument name="dsn" value="#dsn#">
            <cfinvokeargument name="tableName" value="#tableName#">
        </cfinvoke>

Constructing the Final SQL Statement and Querying the Database

Our service endpoint will take the SQL statements from the createSqlForVirtualGrid function and build the final SQL statement to query the database with. SQL Server's row_number is used to have a generic value in which to determine what rows to return. With virtual grids, we only want to return a limited number of rows to the client, in this case, 100, and the row number provides the start and end point of the rows to return. 

The final SQL statement when initially populating this virtual grid will look like this:

SELECT * FROM ( SELECT CountryId ,Country ,Capital ,Currency ,CurrencyName ,ISO2 ,ISO3 ,Flag ,Latitude ,Longitude ,StateId ,State ,StateFlag ,Type ,CityId ,City ,CityLatitude ,CityLongitude ,CityFlag ,ROW_NUMBER() OVER (ORDER BY Country) AS RowNumber FROM [dbo].[ViewCountryStateCity]) vCountryStateCity WHERE RowNumber BETWEEN 0 AND 100

We are also appending any sort statements and where clauses (filters) are constructed by the createSqlForVirtualGrid function and adding an optional logging function. Finally, we will query the database when the final SQL string is constructed.

<!--- Build the over order by statement. Make sure that a closing bracket ')' is at the end of the string. --->
<cfset overOrderStatement = ',ROW_NUMBER() OVER ('>
<cfif sortStatement neq ''>
	<cfset overOrderStatement = overOrderStatement & sortStatement & ")">
<cfelse>
	<!--- Default order by.  --->
	<cfset overOrderStatement = overOrderStatement & defaultOrderByStatement & ")">
</cfif>
<!--- Append it to the sqlStatement --->
<cfset sqlStatement = sqlStatement & " " & overOrderStatement>
<!--- Build the alias for the rownumber(). I am defaulting to 'as rowNumber' --->
<cfset sqlStatement = sqlStatement & " AS RowNumber">
<!--- Append the real and alias table name --->
<cfset sqlStatement = sqlStatement & " FROM [dbo].[" & tableName & "]) " & tableNameAlias>

<!--- Append the additional WHERE clause statement to it if necessary --->
<cfif whereClause neq ''>
	<cfset sqlStatement = sqlStatement & " " & preserveSingleQuotes(whereClause)>
</cfif>

<!--- Log the sql when the logSql is set to true (on top of function) ---> 
<!--- <cfif logSql>
	<cfset filePath = subledgerPath & 'logs'>
	<cffile action="append" addnewline="yes" file="#filePath#/virtualGridSql.txt" output="#Chr(13)##Chr(10)#'#myTrim(sqlStatement)#'#Chr(13)##Chr(10)#" fixnewline="yes">
</cfif> --->

<!--- Testing carriage. If this is not commented out, the grids will not populate.  --->
<cfoutput>#preserveSingleQuotes(whereClause)#</cfoutput>

<cfquery name="data" datasource="#dsn#">
	#preserveSingleQuotes(sqlStatement)#
</cfquery>

Return the Query Results Using the convertCfQuery2JsonStructForVirtualGrid method

Finally, we will convert the ColdFusion query object to JSON using the convertCfQuery2JsonStructForVirtualGrid method found in the KendoUtils ColdFusion component. This code implements our CFJson ColdFusion component to convert the query into JSON. See https://www.gregoryalexander.com/blog/2022/7/12/Convert-a-ColdFusion-Query-into-a-JSON-Object for more information. 

<!--- Using my jsonArray.cfc --->
<cfinvoke component="#application.cfJsonComponentPath#" method="convertCfQuery2JsonStructForVirtualGrid" returnvariable="jsonString" >
	<cfinvokeargument name="queryObj" value="#data#">
	<cfinvokeargument name="includeTotal" value="true">
	<!--- When we use server side paging, we need to override the total and specify a new total which is the sum of the entire query. --->
	<cfinvokeargument name="overRideTotal" value="true">
	<!--- We set the totalNumRecords near the top of the function, however, if the filters were applied, the new total will be the number of records in the data query object. ---> 
	<cfif searchFilter>
		<cfset totalRecords = data.recordcount>
	<cfelse>
		<cfset totalRecords = totalNumRecords>
	</cfif>
	<cfinvokeargument name="newTotal" value="#totalRecords#">
	<!--- The includeDataHandle is used when the format is json (or jsonp), however, the data handle is not included when you want to make a javascript object embedded in the page. ---> 
	<cfinvokeargument name="includeDataHandle" value="true">
	<!--- If the data handle is not used, this can be left blank. If you are going to use a service on the cfc, typically, the value would be 'data'--->
	<cfinvokeargument name="dataHandleName" value="data">
	<!--- Keep the case of the columns the same as the database --->
	<cfinvokeargument name="convertColumnNamesToLowerCase" value="false">
</cfinvoke>

<cfreturn jsonString>

Overview of the createSqlForVirtualGrid Method in the kendoUtils Component

The createSqlForVirtualGrid method injects the JSON from the Kendo Datasource and prepares a SQL statement that is used to query the database. This is a complex method and we will not go over everything, however, I will try to explain the core logic of the code.

Arguments

This function takes 3 arguments: jsonString, dsn, and tableName. These arguments should be self-explanatory. 

<cfargument name="jsonString" type="any" required="yes" hint="Pass in the json string that was passed in the http body. The string should be captured on the page that the kendo grid is posting to like so: cfset requestBody = toString( getHttpRequestData().content ). After the string is passed in, this function will provide a structure with the following elements: take, skip, page, pageSize, whereClause, sortField, and sortDir."> 
<cfargument name="dsn" type="string" required="yes" hint="We neeed to determine the column datatype, we need to have the datasource to determine the datatype for columns."> 
<cfargument name="tableName" type="string" hint="We neeed to determine the column datatype, and need to have the table name to determine the datatype for columns.">
    <!--- Note: the column names will be passed in a struct in the json string.  --->

Method to Determine the Column Datatype

The DbColumnProperyObj is used to determine the column datatype that we are using. We need to figure out the column datatype in order to determine whether to enclose our where clause values with strings. For example, the following WHERE clause will work with integer values (WHERE UserId = 1) whereas if the column datatype is a string we need to enclose the value with quotes like so: WHERE UserName = 'gregory'. The following line of code is used to inspect the column properties. It returns a query object and we are using the datatype value. For more information, see Helper Functions at the bottom of this article.

<!--- Instantiate the column property object. --->
<cfobject component="common.cfc.db.utilities.ColumnProperty" name="DbColumnProperyObj">

Deserialize JSON and Preset Variables

The following code in the createSqlForVirtualGrid deserializes the JSON string and presets the variables used in the method.

<!--- Deserialize the json string. ---> 
<cfset incomingJson = deserializeJson(jsonString, false)>

<!--- Set some params --->
<!--- Output params --->
<cfparam name="take" default="" type="string">
<cfparam name="skip" default="" type="string">
<cfparam name="page" default="" type="string">
<cfparam name="pageSize" default="" type="string">
<cfparam name="sortField" default="" type="string">
<cfparam name="sortDir" default="" type="string">
<cfparam name="sortStatement" default="" type="string">

<!--- Internal params --->
<cfparam name="finalFilterField" default="" type="string">
<cfparam name="finalFilterOperator" default="" type="string">
<cfparam name="finalFilterValue" default="" type="string">
<cfparam name="sqlClauseCombined" default="" type="string">
<cfparam name="searchFilter" default="false" type="boolean">

Reading the Simple String Values from the Kendo Datasource

The take, skip, page and pageSize parameters sent by the Kendo datasource are simple string values and they are accessed using a typical ColdFusion structure syntax (incomingJson.take for example).

<!--- Set the vars. --->
<cfset take = incomingJson.take>
<cfset skip = incomingJson.skip>
<cfset page = incomingJson.page>
<cfset pageSize = incomingJson.pageSize>

Looping Through the Kendo Sort Object 

If the sort filter array is present in the JSON, we will loop through the object to create the last ORDER statement used to query the database. The sort array is accessed using incomingJson['sort'] and the items in a nested array are i['field'] and i['dir']. Field is the database column that we need to sort by and dir is the direction of the sort (either ASC or DESC for example). There can be many objects in the sort array.

<!--- We also need to get at the sort arguments which might be held in an array. 
Note: 7/7/2017 the kendo grids now support multiple sort options. The new logic will be sent in like so: 
Single sort: [{field: "bar70id", dir: "asc"}]
Multiple sorts: sort: [{field: "bar70id", dir: "asc"}, {field: "faspostdatekey", dir: "asc"}]--->
<cfif structKeyExists(incomingJson, "sort")>
	<cfparam name="sortStatement" default="" type="any">
	<cfloop array="#incomingJson['sort']#" index="i">
		<cfset sortField = i['field']>
		<cfset sortDir = i['dir']>
		<!--- For every element found in the sort array, put in a comma and keep the prior sort statement that was built. --->
		<cfif sortStatement eq ''>
			<cfset sortStatement = "ORDER BY " & sortField & " " & sortDir>
		<cfelse>
			<cfset sortStatement = sortStatement & ", " & sortField & " " & sortDir>
		</cfif>
		<cfoutput>sortField: #sortField# sortDir: #sortDir#<br/></cfoutput>
	</cfloop>
</cfif>

Looping Through the Kendo Filters Object

The filters object specifies which filters to apply to the data. These filters will be used to construct SQL WHERE clauses when querying the database. 

The following code may at first glance look daunting, but, we have covered the essential Kendo filter objects and all that we are doing here is constructing SQL WHERE clauses to handle filters that the users have made. I have made a lot of inline comments to make it easier to figure out what is going on. I have also programmed debugging carriages and logging features- if you're using ColdFusion you can turn on debugging and output the results.

There are two major loops in this code as each column can either have one or a pair of filters. Most of the examples that we used above, such as finding cities within the US, only had one filter object. However, when we wanted to perform a search finding cities that have the same latitude as Los Angeles- we used a pair of arguments: we searched for cities with latitudes greater than 32.03 and less than 32.07. In this query, we have to loop through both the parent filters object and the sub-filter object.

The filter.filters object can have many different filters. Every filterable column in the grid can have a pair of arguments. We also need to inspect the column datatype in order to determine whether to apply quotes to the values clause. We also need to loop through all of the filter operators (equals to, greater than, less then, etc) to handle the WHERE clauses. 

After this function constructs the SQL, it will return the SQL statement back as a ColdFusion structure to the service endpoint.

<!--- Dig in the first filters object. If there were only on set of filters, the filter data will be in this part of the object.
The first structure is easy to work with. But, the oject gets very complex when looking in the filters array. The filers array can have multiple structures and arrays in it --->

<cfif structKeyExists(incomingJson, "filter")>
	<!--- Set a variable that we will pass back indicating that a 'filter' was made. This variable will determine how the calling template processes the total records count. ---> 
	<cfset searchFilter = true>
	<!--- Get the values of the items that we know exist--->
	<!--- Get the 'logic' array (and, or etc) --->
	<cfset sqlLogicStatement = incomingJson['filter']['logic']>
	<!--- Traverse to the filters.filters array. ---> 
	<cfset filtersArray = incomingJson['filter']['filters']>
	<!--- There will be 2 loops thru the filter array. We need to put the logic statement between the two clauses (name = 'gregory' AND lname = 'alexander'). Set a loop counter here.--->
	<cfset filterLoopCount=1>
	<cfloop array="#filtersArray#" index="i">
		<!--- There may be multiple structures in the filters object that cause an error. If there are, ignore them.  --->
		<!--- <cftry> --->
			<cfset filterField = i['field']>
			<cfset filterOperator = i['operator']>
			<cfset filterValue = i['value']>
			<!--- <cfoutput>filterValue=#filterValue#</cfoutput> --->

			<!--- Determine if the value should be put in single qoutes. --->
			<cfinvoke component="#DbColumnProperyObj#" method="getDataType" returnvariable="dataType">
				<cfinvokeargument name="dsn" value="#dsn#">
				<cfinvokeargument name="table" value="#tableName#">
				<cfinvokeargument name="column" value="#filterField#">
			</cfinvoke>
			<!--- <cfoutput>'#dataType#'</cfoutput> --->
			<cfif dataType contains 'date'>
				<!--- Cast the field --->
				<cfset filterField = 'cast(' & filterField & ' AS DATE)'>
				<!--- Convert the iso date into a readable format for sql --->
				<cfset filterValue = dateFormat(isoToDateTime(filterValue), 'mm-dd-yyyy')>
			</cfif>

			<!--- Note!!! Copy this block below in the exact same spot! Low priority- I'll fix after deadlines? --->
			<!--- Build a sql WHERE statement. --->
			<cfswitch expression="#filterOperator#">
				<cfcase value="eq">
					<!--- For eq and neq, use a qouted filter value --->
					<cfif dataType contains 'char' or dataType contains 'text' or dataType eq 'decimal' or dataType contains 'date'>
						<!--- Enclose the value with qoutes. --->
						<cfset filterValue = "'" & filterValue & "'">
					</cfif>
					<cfset sqlClause = filterField & " = " & filterValue>
				</cfcase>
				<cfcase value="neq">
					<!--- For eq and neq, use a qouted filter value --->
					<cfif dataType contains 'char' or dataType contains 'text' or dataType eq 'decimal'>
						<!--- Enclose the value with qoutes. --->
						<cfset filterValue = "'" & filterValue & "'">
					</cfif>
					<cfset sqlClause = filterField & " <> " & filterValue>
				</cfcase>
				<!--- The following cases are generally numeric, however, longitude and latitudes are sometimes stored as varchars --->
				<cfcase value="gt">
					<!--- For eq and neq, use a qouted filter value --->
					<cfif dataType contains 'char' or dataType contains 'text' or dataType eq 'decimal'>
						<!--- Enclose the value with qoutes. --->
						<cfset filterValue = "'" & filterValue & "'">
					</cfif>
					<cfset sqlClause = filterField & " > " & filterValue>
				</cfcase>
				<cfcase value="gte">
					<cfif dataType contains 'char' or dataType contains 'text' or dataType eq 'decimal'>
						<!--- Enclose the value with qoutes. --->
						<cfset filterValue = "'" & filterValue & "'">
					</cfif>
					<cfset sqlClause = filterField & " >= " & filterValue>
				</cfcase>
				<cfcase value="lt">
					<cfif dataType contains 'char' or dataType contains 'text' or dataType eq 'decimal'>
						<!--- Enclose the value with qoutes. --->
						<cfset filterValue = "'" & filterValue & "'">
					</cfif>
					<cfset sqlClause = filterField & " < " & filterValue>
				</cfcase>
				<cfcase value="lte">
					<cfif dataType contains 'char' or dataType contains 'text' or dataType eq 'decimal'>
						<!--- Enclose the value with qoutes. --->
						<cfset filterValue = "'" & filterValue & "'">
					</cfif>
					<cfset sqlClause = filterField & " <= " & filterValue>
				</cfcase>
				   <!---  Like statements will not enclose the filterValue with qoutes. --->
				<cfcase value="startswith"><cfset sqlClause = filterField & " LIKE '" & filterValue & "%'"></cfcase>
				<cfcase value="contains"><cfset sqlClause = filterField & " LIKE '%" & filterValue & "%'"></cfcase>
				<cfcase value="doesnotcontain"><cfset sqlClause = filterField & " NOT LIKE '%" & filterValue & "%'"></cfcase>
				<cfcase value="endswith"><cfset sqlClause = filterField & " LIKE '%" & filterValue & "'"></cfcase>
				<!--- The following cases do not need to pass a value. --->
				<cfcase value="isnotnull"><cfset sqlClause = filterField & " IS NOT NULL"></cfcase>
				<!--- 
				Write out ' datalength(x)=0' ('datalength(agencyrateid)=0' for example).
				This prevents data type errors when using where x = '' on numeric values.
				--->
				<cfcase value="isempty"><cfset sqlClause = " datalength(" & filterField & ") = 0"></cfcase>
				<cfcase value="isnotempty"><cfset sqlClause = " datalength(" & filterField & ") > 0"></cfcase>
			</cfswitch>
			<!--- Using isnull in a case statement causes the template to fail. --->
			<cfif filterOperator eq "isnull">
				<cfset sqlClause = filterField & " IS NULL">
			</cfif>
			<!--- !!! Copy End code --->

			<!--- 
			<cfoutput>
				<cfif filterLoopCount eq 2>
					#sqlLogicStatement#
				</cfif> 
				#sqlClause#
			</cfoutput> 
			--->
			<cfif filterLoopCount eq 1>
				<cfset sqlClauseCombined = sqlClauseCombined & " WHERE ">
			<cfelseif filterLoopCount gte 2>
				<cfset sqlClauseCombined = sqlClauseCombined & " " & uCase(sqlLogicStatement) & " ">
			</cfif>
			<cfset sqlClauseCombined = sqlClauseCombined & sqlClause>
			<!--- Increment the counter. --->
			<cfset filterLoopCount = filterLoopCount + 1>
			<!--- <cfcatch type="any"></cfcatch>
		</cftry> --->
	</cfloop><!--- <cfloop array="#filtersArray#" index="i"> --->

	<!--- If there are multiple filters made, get the next filters array if they exist. --->
	<cfset subFilterLoopCount=1>
	<cfset prevParentLoopCount=0>
	<cfloop from="1" to="10" index="parentLoop">
		<cftry>
			<cfif arrayIsDefined(filtersArray[parentLoop]['filters'], 1)>
				<cfset subFiltersArray = filtersArray[parentLoop]['filters']>
				<!--- Loop thru it just like we did before. It should be a carbon copy of the previous filters array. ---> 
				<cfloop array="#subFiltersArray#" index="i">
					<!--- <cftry> --->
						<cfset filterField = i['field']>
						<cfset filterOperator = i['operator']>
						<cfset filterValue = i['value']>

						<!--- Determine if the value should be put in single qoutes. --->
						<cfinvoke component="#DbColumnProperyObj#" method="getColumnDataType" returnvariable="dataType">
							<cfinvokeargument name="dsn" value="#dsn#">
							<cfinvokeargument name="table" value="#tableName#">
							<cfinvokeargument name="columnName" value="#filterField#">
						</cfinvoke>
						<!--- Wrap the filterValue with qoutes if necessary. ---> 
						<cfif dataType eq 'varchar'>
							<!--- Enclose the value with qoutes. --->
							<cfset filterValue = "'" & filterValue & "'">
						</cfif>


						<!--- Note!!! Copy the block ABOVE in the exact same spot! Low priority- I'll fix after deadlines? --->
						<!--- Build a sql WHERE statement. --->
						<cfswitch expression="#filterOperator#">
							<cfcase value="eq">
								<!--- For eq and neq, use a qouted filter value --->
								<cfif dataType contains 'char' or dataType contains 'text' or dataType eq 'decimal'>
									<!--- Enclose the value with qoutes. --->
									<cfset filterValue = "'" & filterValue & "'">
								</cfif>
								<cfset sqlClause = filterField & " = " & filterValue>
							</cfcase>
							<cfcase value="neq">
								<!--- For eq and neq, use a qouted filter value --->
								<cfif dataType contains 'char' or dataType contains 'text' or dataType eq 'decimal'>
									<!--- Enclose the value with qoutes. --->
									<cfset filterValue = "'" & filterValue & "'">
								</cfif>
								<cfset sqlClause = filterField & " <> " & filterValue>
							</cfcase>
							<!--- The following cases are generally numeric, however, longitude and latitudes are sometimes stored as varchars --->
							<cfcase value="gt">
								<!--- For eq and neq, use a qouted filter value --->
								<cfif dataType contains 'char' or dataType contains 'text' or dataType eq 'decimal'>
									<!--- Enclose the value with qoutes. --->
									<cfset filterValue = "'" & filterValue & "'">
								</cfif>
								<cfset sqlClause = filterField & " > " & filterValue>
							</cfcase>
							<cfcase value="gte">
								<cfif dataType contains 'char' or dataType contains 'text' or dataType eq 'decimal'>
									<!--- Enclose the value with qoutes. --->
									<cfset filterValue = "'" & filterValue & "'">
								</cfif>
								<cfset sqlClause = filterField & " >= " & filterValue>
							</cfcase>
							<cfcase value="lt">
								<cfif dataType contains 'char' or dataType contains 'text' or dataType eq 'decimal'>
									<!--- Enclose the value with qoutes. --->
									<cfset filterValue = "'" & filterValue & "'">
								</cfif>
								<cfset sqlClause = filterField & " < " & filterValue>
							</cfcase>
							<cfcase value="lte">
								<cfif dataType contains 'char' or dataType contains 'text' or dataType eq 'decimal'>
									<!--- Enclose the value with qoutes. --->
									<cfset filterValue = "'" & filterValue & "'">
								</cfif>
								<cfset sqlClause = filterField & " <= " & filterValue>
							</cfcase>
							<!---  Like statements will not enclose the filterValue with qoutes. --->
							<cfcase value="startswith"><cfset sqlClause = filterField & " LIKE '" & filterValue & "%'"></cfcase>
							<cfcase value="contains"><cfset sqlClause = filterField & " LIKE '%" & filterValue & "%'"></cfcase>
							<cfcase value="doesnotcontain"><cfset sqlClause = filterField & " NOT LIKE '%" & filterValue & "%'"></cfcase>
							<cfcase value="endswith"><cfset sqlClause = filterField & " LIKE '%" & filterValue & "'"></cfcase>
							<!--- The following cases do not need to pass a value. --->
							<cfcase value="isnotnull"><cfset sqlClause = filterField & " IS NOT NULL"></cfcase>
							<!--- 
							Write out ' datalength(x)=0' ('datalength(agencyrateid)=0' for example).
							This prevents data type errors when using where x = '' on numeric values.
							--->
							<cfcase value="isempty"><cfset sqlClause = " datalength(" & filterField & ") = 0"></cfcase>
							<cfcase value="isnotempty"><cfset sqlClause = " datalength(" & filterField & ") > 0"></cfcase>
						</cfswitch>
						<!--- Using isnull in a case statement causes the template to fail. --->
						<cfif filterOperator eq "isnull">
							<cfset sqlClause = filterField & " IS NULL">
						</cfif>
						<!--- !!! Copy End code --->

						<!--- 
						<cfoutput>
							<br/>subFilterLoopCount:#subFilterLoopCount# parentLoop:#parentLoop# prevParentLoopCount:#prevParentLoopCount#
							<cfif subFilterLoopCount eq 1> 
								AND 
							<cfelseif parentLoop eq prevParentLoopCount>
								#uCase(sqlLogicStatement)#
							<cfelse>
								AND 
							</cfif> 
							#sqlClause#
						</cfoutput> 
						--->
						<cfif subFilterLoopCount eq 1>
							<!--- Note: the first loop above may or may not be present. If it is, the where statement will be built, if not, add it. ---> 
							<cfif find('WHERE', sqlClauseCombined) eq 0>
								<cfset sqlClauseCombined = sqlClauseCombined & " WHERE ">
							<cfelseif sqlClauseCombined neq ''>
								<cfset sqlClauseCombined = sqlClauseCombined & " AND ">
							</cfif>
						<cfelseif parentLoop eq prevParentLoopCount>
							<cfset sqlClauseCombined = sqlClauseCombined &  " " & uCase(sqlLogicStatement & " ")>
						<cfelse>
							<cfset sqlClauseCombined = sqlClauseCombined & " AND ">
						</cfif>
						<cfset sqlClauseCombined = sqlClauseCombined & sqlClause>
						<!--- Save the value of the current counter --->
						<cfset prevParentLoopCount = parentLoop>
						<!--- Increment our counter. --->
						<cfset subFilterLoopCount = subFilterLoopCount + 1>
						<!--- <cfcatch type="any"></cfcatch>
					</cftry> --->
				</cfloop><!--- <cfloop array="#subFiltersArray#" index="i"> --->
			</cfif><!--- <cfif arrayIsDefined(filtersArray[parent]['filters'], 1)> --->
			<cfcatch type="any"></cfcatch>
		</cftry>
	</cfloop><!--- <cfloop from="1" to="10" index="parentLoop"> --->
</cfif><!--- <cfif structKeyExists(incomingJson, "filter")> --->

Determine the Start and End Rows of the Query

The following code simply adds the skip and take parameters sent by the Kendo data source to determine the start and end row and constructs a SQL WHERE clause like so: 'WHERE RowNumber BETWEEN x and x'

<cfset rowStart = skip><!--- The rowStart is generally the skip var coming thru the json reqest. --->
<cfset rowEnd = (skip + take)>

<!--- Post processing and cleanup --->
<!--- If the user did not choose to filter anything, the sqlClauseStatement should be an empty string. If this is the case, build the WHERE clause and use the skip argument sent via the grid. --->
<cfif sqlClauseCombined eq ''>
	<cfset sqlClauseCombined = "WHERE RowNumber BETWEEN " & rowStart & " AND " & rowEnd>
</cfif>

Return a Structure of SQL Statements Back to the Service Endpoint

Finally, our createSqlForVirtualGrid method will return the various SQL structures back to the calling endpoint. The service endpoint will take these SQL structures to construct the final SQL statement.

<!--- Finally, create a ColdFusion structure object and pass it back. --->
<cfscript> 
	sqlForVirtualGrid = structNew(); 
	structInsert(sqlForVirtualGrid, "take", take); 
	structInsert(sqlForVirtualGrid, "skip", skip); 
	structInsert(sqlForVirtualGrid, "page", page); 
	structInsert(sqlForVirtualGrid, "pageSize", pageSize); 
	structInsert(sqlForVirtualGrid, "searchFilter", searchFilter); 
	structInsert(sqlForVirtualGrid, "whereClause", sqlClauseCombined); 
	structInsert(sqlForVirtualGrid, "sortStatement", sortStatement); 
	// Include the sql sript for debugging.
	structInsert(sqlForVirtualGrid, "sqlClauseCombined", sqlClauseCombined);  
</cfscript> 

<cfreturn sqlForVirtualGrid>

Helper Functions

Method to Determine Column Datatype

The DbColumnProperyObj is used to determine the column datatype that we are using. We need to figure out the column datatype in order to determine whether to enclose our where clause values with strings. For example, the following WHERE clause will work with integer values (WHERE UserId = 1) whereas if the column datatype is a string we need to enclose the value with quotes like so: WHERE UserName = 'gregory'. 

The  DbColumnProperyObj uses the following SQL query will determine the properties of a table. It returns a query object and we are using the datatype value:

    <cfquery name="data" datasource="#dsn#">
        SELECT 
            c.name 'ColumnName',
            t.Name 'DataType',
            c.max_length 'MaxLength',
            c.precision,
            c.scale,
            c.is_nullable,
            ISNULL(i.is_primary_key, 0) 'PrimaryKey',
            c.default_object_id as ColumnDefault,
			c.is_computed as IsComputed
        FROM    
            sys.columns c
        INNER JOIN 
            sys.types t ON c.user_type_id = t.user_type_id
        LEFT OUTER JOIN 
            sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        LEFT OUTER JOIN 
            sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
        WHERE 0=0
            AND c.object_id = OBJECT_ID(<cfqueryparam value="#table#" cfsqltype="cf_sql_varchar">)
            AND c.name = (<cfqueryparam value="#column#" cfsqltype="cf_sql_varchar">)
    </cfquery>

The convertCfQuery2JsonStructForVirtualGrid to Transform a ColdFusion Query Object into JSON

Our CFJson ColdFusion component has a specialized function to transform a ColdFusion query object into JSON for Kendo Virtual Grids. The following method takes a ColdFusion query object and converts the data into JSON that the Kendo Datasource for virtual grids can consume. 

<cffunction name="convertCfQuery2JsonStructForVirtualGrid" access="public" output="true" hint="convert a ColdFusion query object into to array of structures returned as a json array. This has been specially designed for for Kendo virtual Grids">
	<cfargument name="queryObj" type="query" required="true">
	<cfargument name="contentType" type="string" required="false" default="json">
	<cfargument name="includeDataHandle" type="boolean" required="false" default="true">
	<cfargument name="dataHandleName" type="string" required="false" default="data">
	<cfargument name="includeTotal" type="boolean" required="false" default="true">
	<!--- Optional arguments to over ride the total which is used when the grids use serverside paging. ---> 
	<cfargument name="overRideTotal" type="boolean" required="false" default="false">
	<cfargument name="newTotal" type="any" default="false" hint="On grids that use serverside paging on kendo grids, we need to override the total.">
	<!--- Optional arguments to enable the function to clean up the HTML in the notes column for the grid. Without these arguments, the html which formats the data will also be displayed in the grid. --->
	<cfargument name="removeStringHtmlFormatting" type="boolean" required="false" default="true">
	<!--- Note: if you are trying to clean strings, it will fail if the datatype is anything other than a string. You must also provide the column name that you want to clean. ---> 
	<cfargument name="columnThatContainsHtmlStrings" type="string" required="false" default="">
	<cfargument name="convertColumnNamesToLowerCase" type="boolean" default="false" hint="Because Javascript is case sensitive, you may just want to convert everything to lower case.">

	<cfset var rs = {} /> <!--- implicit structure creation --->
	<cfset rs.results = [] /> <!--- implicit array creation --->
	<!--- Get the columns. ---> 
	<cfset rs.columnList = lCase(listSort(queryObj.columnlist, "text" )) />
	<cfif not convertColumnNamesToLowerCase>
		<!--- Get the column label, which is the actual name of the column that is not forced into uppercase as the columnList is. Note: the getMeta() function will return a two column array object with the numeric index along with the value. We need to convert this into a list. --->
		<cfset realColumnList = arrayToList(queryObj.getMeta().getcolumnlabels())>
	</cfif>

	<!--- Loop over the query object and build a structure of arrays --->
	<cfloop query="queryObj">
		<cfset rs.temp = {} /><!--- temporary structure --->
		<cfloop list="#rs.columnList#" index="rs.col">
			<!--- To remove any formatting and get the string, we will use a Java object to turn an html object into a valid xml doc, and then use xml processing to get to the underlying string. --->
			<cfif convertColumnNamesToLowerCase>
				<!--- Get the lower cased column name (it was forced into a lower case up above). --->
				<cfset columnName = rs.col>
			<cfelse>
				<!--- Find the index in our realColumnList --->
				<cfset realColumnNameIndex = listFindNoCase(realColumnList, rs.col)>
				<!--- Get at the value. ---> 
				<cfset columnName = listGetAt(realColumnList, realColumnNameIndex)>
			</cfif>                
			<cfset columnValue = queryObj[rs.col][queryObj.currentrow]>
			<cfif removeStringHtmlFormatting>
				<cfif columnName eq columnThatContainsHtmlStrings>
					<cfset firstPass = getStringFromHtml(columnValue)>
					<!--- We have to do two passes here unfortunately. The first pass returns a string with the em tags, the 2nd pass should clear all formatting. Will revisit this when I have more time.  --->
					<cfset columnValue = getStringFromHtml(firstPass)>
				</cfif>
			</cfif>
			<cfset rs.temp[columnName] = columnValue />
		</cfloop>
		<cfset arrayAppend( rs.results, rs.temp ) />
	</cfloop>

	<!--- Build the final structure. ---> 
	<cfset rs.data = {} /> <!--- final structure --->

	<!--- Include the data handle if needed --->
	<cfif includeDataHandle>
		<cfset rs.data[dataHandleName] = rs.results />
	<cfelse>
		<cfset rs.data = rs.results />
	</cfif>

	<!--- Return the recordcount. This is needed on certain grids to display the total number of records. --->
	<cfif includeTotal>
		<cfif overRideTotal>
			<!--- Note: on virtual grids, when you don't include the total (which other than debugging, is never the case, there will be an error here (can't convert 'total' to a number). If debugging, put some random numeric value here.  --->
			<cfset rs.data["total"] = newTotal>
		<cfelse>
			<cfset rs.data["total"] = queryObj.recordcount>
		</cfif>
	</cfif>

	<cfreturn serializeJSON(rs.data) />
</cffunction>

The JSON response returned to the client looks like so: 

{"total":129943,"data":[{"Currency":"AFN","CityFlag":1,"CityId":50,"CountryId":1,"Longitude":65.00000000,"CityLongitude":65.123760,"CurrencyName":"Afghan afghani","State":"Faryab","Flag":1,"ISO3":"AFG","ISO2":"AF","RowNumber":1,"StateFlag":1,"City":"Andkhoy","Country":"Afghanistan","StateId":3889,"CityLatitude":36.952930,"Latitude":33.00000000,"Type":"","Capital":"Kabul"},{"Currency":"AFN","CityFlag":1,"CityId":51,"CountryId":1,"Longitude":65.00000000,"CityLongitude":71.146970,"CurrencyName":"Afghan afghani","State":"Kunar","Flag":1,"ISO3":"AFG","ISO2":"AF","RowNumber":2,"StateFlag":1,"City":"Asadabad","Country":"Afghanistan","StateId":3876,"CityLatitude":34.873110,"Latitude":33.00000000,"Type":"","Capital":"Kabul"},{"Currency":"AFN","CityFlag":1,"CityId":52,"CountryId":1,"Longitude":65.00000000,"CityLongitude":71.533330,"CurrencyName":"Afghan afghani","State":"Badakhshan","Flag":1,"ISO3":"AFG","ISO2":"AF","RowNumber":3,"StateFlag":1,"City":"Ashk?sham","Country":"Afghanistan","StateId":3901,"CityLatitude":36.683330,"Latitude":33.00000000,"Type":"","Capital":"Kabul"},{"Currency":"AFN","CityFlag":1,"CityId":53,"CountryId":1,"Longitude":65.00000000,"CityLongitude":68.015510,"CurrencyName":"Afghan afghani","State":"Samangan","Flag":1,"ISO3":"AFG","ISO2":"AF","RowNumber":4,"StateFlag":1,"City":"A?bak","Country":"Afghanistan","StateId":3883,"CityLatitude":36.264680,"Latitude":33.00000000,"Type":"","Capital":"Kabul"},{"Currency":"AFN","CityFlag":1,"CityId":54,"CountryId":1,"Longitude":65.00000000,"CityLongitude":68.708290,"CurrencyName":"Afghan afghani","State":"Baghlan","Flag":1,"ISO3":"AFG","ISO2":"AF","RowNumber":5,"StateFlag":1,"City":"Baghl?n","Country":"Afghanistan","StateId":3875,"CityLatitude":36.130680,"Latitude":33.00000000,"Type":"","Capital":"Kabul"}]}

The CFJson component can be downloaded from GitHub at https://github.com/GregoryAlexander77/CfJson/blob/main/CfJson.cfc.

Further Reading: