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.
Feb 16, 2009 at 5:39 AM 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:
Feb 16, 2009 at 5:39 AM 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 /><cfquery name="rs" dbtype="query"><br /> SELECT<br /> ID<br /> , CityName<br /> FROM (<br /> SELECT<br /> ID<br /> , CityName<br /> , CASE WHEN ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.SelectedValue#" /><br /> THEN 0<br /> ELSE 1<br /> END AS Sort<br /> FROM queryCity (NOLOCK)<br /> WHERE State_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.State_ID#" /><br /> ) d<br /> ORDER BY Sort, CityName<br /></cfquery> <br /><br />WHERE queryCity is already a query
Feb 16, 2009 at 8:12 AM 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.