cfselect, binding, and selectedvalues

ColdFusion's cfselect doesn't support the selectedvalue argument when using binding. I don't like the JavaScript solutions out there, so here's a simple SQL solution.

If you have a cfselect like this one that makes a call to a remote CFC then the selectedvalue is completely ignored when the field is populated:

<cfselect 
    name="city_id" 
    bind="cfc:cfcs.Remote.getCities({inventoryDetails:state_id})"
    bindOnLoad="true"
    value="ID"
    display="CityName"
    selected="#MyProperty.getCity_ID()#"
/>

Remote.cfc:

<cffunction name="getCities" access="remote" returntype="query" hint="Returns a query of cities for a specific state">
    <cfargument name="state_id" type="numeric" required="true" />
    <cfset var rs = "" />

    <cfif Val(Arguments.State_ID) IS 0>
        <cfset rs = QueryNew("ID, CityName") />
        <cfset QueryAddRow(rs) />
        <cfset QuerySetCell(rs, "ID", 0) />
        <cfset QuerySetCell(rs, "CityName", "Choose a state first...") />
    <cfelse>
        <cfquery name="rs" datasource="#DSN#">
        SELECT
            ID
            , CityName
        FROM City (NOLOCK)
        WHERE State_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.State_ID#" />
        ORDER BY CityName
        </cfquery>
    </cfif>

    <cfreturn rs />

</cffunction>

I've seen JavaScript solutions from Ray Camden and others to make this work, but it always just seemed like too much work, or involved changing/overriding core cfajax files. So why not just have the result you want be the first value returned from the query? With a simple CASE statement we can bubble our desired value to the top of the list so that when the select is populated it's the first (and by default selected) element. I just need to change my binding call and my function:

<cfselect 
    name="city_id"
    bind="cfc:cfcs.Remote.getCities({inventoryDetails:state_id}, #MyProperty.getCity_ID()#)"
    bindOnLoad="true" 
    value="ID" 
    display="CityName"
 />

Remote.cfc:

<cffunction name="getCities" access="remote" returntype="query" hint="Returns a query of cities for a specific state">
    <cfargument name="state_id" type="numeric" required="true" />
    <cfargument name="selectedValue" type="numeric" required="false" default="0" />
    <cfset var rs = "" />

    <cfif Val(Arguments.State_ID) IS 0>
        <cfset rs = QueryNew("ID, CityName") />
        <cfset QueryAddRow(rs) />
        <cfset QuerySetCell(rs, "ID", 0) />
        <cfset QuerySetCell(rs, "CityName", "Choose a state first...") />
    <cfelse>
        <cfquery name="rs" datasource="#DSN#">
        SELECT
            ID
            , CityName
        FROM (
            SELECT
                ID
                , CityName
                , CASE WHEN ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.SelectedValue#" />
                    THEN 0
                    ELSE 1
                END AS Sort
            FROM City (NOLOCK)
            WHERE State_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.State_ID#" />
        ) d
        ORDER BY Sort, CityName
        </cfquery>
    </cfif>

    <cfreturn rs />

</cffunction>

Now when the query is returned, the ID that was passed as the selectedvalue argument will have a sort value of 0, and every other row will have a sort value of 1. Our ORDER BY statement then bubbles the first result to the top of the list.

3 responses to “cfselect, binding, and selectedvalues”

  1. LordRhumSifflarLordRhumSifflar Says:
    Well done.

    But, Do you know if (and how) we can do the same things with queryOfQuery?
    I've got an error executing database when i try to use this kind of query:
    <cfquery name="rs" dbtype="query">
    SELECT
    ID
    , CityName
    FROM (
    SELECT
    ID
    , CityName
    , CASE WHEN ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.SelectedValue#" />
    THEN 0
    ELSE 1
    END AS Sort
    FROM queryCity (NOLOCK)
    WHERE State_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.State_ID#" />
    ) d
    ORDER BY Sort, CityName
    </cfquery>

    WHERE queryCity is already a query

    LordRhumSifflar Says:
    Well done.<br /><br />But, Do you know if (and how) we can do the same things with queryOfQuery? <br />I've got an error executing database when i try to use this kind of query: <br />&lt;cfquery name=&quot;rs&quot; dbtype=&quot;query&quot;&gt;<br /> SELECT<br /> ID<br /> , CityName<br /> FROM (<br /> SELECT<br /> ID<br /> , CityName<br /> , CASE WHEN ID = &lt;cfqueryparam cfsqltype=&quot;cf_sql_integer&quot; value=&quot;#Arguments.SelectedValue#&quot; /&gt;<br /> THEN 0<br /> ELSE 1<br /> END AS Sort<br /> FROM queryCity (NOLOCK)<br /> WHERE State_ID = &lt;cfqueryparam cfsqltype=&quot;cf_sql_integer&quot; value=&quot;#Arguments.State_ID#&quot; /&gt;<br /> ) d<br /> ORDER BY Sort, CityName<br />&lt;/cfquery&gt; <br /><br />WHERE queryCity is already a query

  2. Daniel ShortDaniel Short Says:
    There are a few problems with that query:

    1. You can't do a subselect in a QofQ.

    2. The (nolock) keywords aren't necessary (and will throw an error).

    3. You can't use a case statement in a QofQ.

    So to get around those three issues you can use the following query:

    <cfquery name="rs" dbtype="query">
    SELECT
       ID
       , CityName
       , 0 AS Sort
    FROM queryCity
    WHERE ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.SelectedValue#" />
    UNION ALL
    SELECT
       ID
       , CityName
       , 1 AS SORT
    FROM queryCity
    WHERE State_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.State_ID#" />
    ORDER BY Sort, CityName
    </cfquery>


    The way to get around most odd issues with QofQ limitations is clever use of Unions.
  3. shekarshekar Says:
    Thanks a lot daniel..fixed a week's struggle..

Leave a Reply

Leave this field empty:

Powered by Mango Blog.