How to use select2 infinite scroll with coldfusion?

Question

http://ivaynberg.github.com/select2/#infinite the example given isn't explained very well and I have no idea what is happening on the back end to produce those results.

Edit: I have changed my cfc to return a limited amount of rows from the query. I also appended the total row count hoping that would be called from the ajax in data.total.

cfc:

<cffunction name="GetClientsByName"
    access="remote"
    returntype="string"
    output="true"
    hint="get clients from search term">

    <cfargument name="name" type="string" required="yes">
    <cfargument name="page" type="numeric">
    <cfargument name="page_limit" type="numeric">

    <cfset var start = (arguments.page * arguments.page_limit) - arguments.page_limit + 1>
    <cfset var end = start + arguments.page_limit>

    <cfset var util = createObject("component", "/surveymanagement/JSONUtil")>
    <cfset var results = arrayNew(1)>
    <cfset var elem = "">
    <cfset var total = "">

    <cfquery name="GetClientsByName" datasource="#application.dsn#">
        SELECT client_id, client_name
        FROM Clients
        WHERE client_name LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.name#%">
        ORDER BY client_name
    </cfquery>

    <cfset total = structNew()>
    <cfset total["total"] = GetClientsByName.RecordCount>

    <cfloop query="GetClientsByName" startrow="#start#" endrow="#end#">
        <cfset elem = structNew()>
        <cfset elem["id"] = GetClientsByName.client_id>
        <cfset elem["text"] = GetClientsByName.client_name>
        <cfset arrayAppend(results, elem)>
    </cfloop>

    <cfset arrayAppend(results, total)>

    <cfcontent type="application/json" reset="true"><cfoutput>#util.serializeJSON(results)#</cfoutput><cfabort />
</cffunction>

I believe I need to alter my sql with the cfarguments page and page_limit, but I don't know what to do with them. How should I go about limiting the rows return by page and then adding the next page of rows?

js:

$(".select").select2({
    allowClear: true,
    blurOnChange: true,
    openOnEnter: false,
    ajax: {
        url: "/surveymanagement/admin/client.cfc",
        dataType: 'json',
        quietMillis: 100,
        data: function (term, page) {
            return {
                method: "GetClientsByName",
                name: term,
                page_limit: 10,
                page: page
            };
        },
        results: function (data, page) {
            var more = (page * 10) < data.total;

            return { results: data, more: more };
        }
    }
});

It would also be nice if someone found a tutorial on how select2 incorporates infinite scroll.

I believe my problem lies in the results section of the ajax call, I am not sure though.

Answer 1

Server Side

The back end page must return a JSON structure containing two values:

• Total records found
• The results (ie. an array of structures)

By default, the plugin expects the result structures to contain two keys: "id" and "text". (Technically you can use different key names. However, if you use different names, you must code a custom function(s) to format the results ie formatResult and formatSelection.)

Sample:

     {"clients":[{"text":"client name ABC","id":112}],"total":1}

Code:

    <cffunction name="getClientsByName" access="remote" returntype="string" output="false">
        <cfargument name="name" type="string" required="yes">
        <cfargument name="page" type="numeric" required="true">
        <cfargument name="page_limit" type="numeric" default="10">

        <cfset var startRow = (arguments.page * arguments.page_limit) - arguments.page_limit + 1>
        <cfset var endRow = startRow + arguments.page_limit>

        <cfset var util = createObject("component", "test.jsonUtil")>
        <cfset var getClientsByName = "">
        <cfset var results = structNew()>
        <cfset var clients = arrayNew(1)>
        <cfset var elem = "">

        ... run db query ....

        <!--- use default keys: "id" and "text" --->  
        <cfloop query="getClientsByName" startRow="#startRow#" endRow="#endRow#">
            <cfset elem = structNew()>
            <cfset elem["id"] = getClientsByName.client_id>
            <cfset elem["text"] = getClientsByName.client_name>
            <cfset arrayAppend(clients, elem)>
        </cfloop>

         <!--- package the results into a structure --->  
        <cfset results["total"] = getClientsByName.recordCount>
        <cfset results["clients"] = clients>

        <cfcontent type="application/json" reset="true">
        <cfoutput>#util.serializeJSON(results)#</cfoutput><cfabort />
    </cffunction>

Client Side

Your javascript only needs a slight tweak. Since data is a structure, you must add the name of the key containing the results, ie

          return { results: data.clients, more: more };

... instead of

          return { results: data, more: more };

Optimization:

Having said that, the current code is inefficient because it retrieves the entire table on every request. It is more efficient to do the pagination on the database side. So the database never returns more than the 10 records at one time. The exact syntax is database specific:

• SQL Server pagination
• MySQL pagination

(BTW, many things in SQL vary by vendor. So it is always good to include your database type and version in the tags when a question involves queries :)