December 14th, 2013, 12:14 PM
Keep the list order when using IN (<cfqueryparam list="xyz" ...)
I am passing a list of 25 SKU's to a cfquery and need to return the data pulled in the same order that the list is in. When I pass the list to the query in the where statement (see below) the order the items come out of the query are not in the same order:
"where products.pid in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#skusfound#" list="yes">)"
The only way I can get this to work is to do a loop around the list and query each item one-by-one. Unfortunately, this method is much slower than using a <cfoutput query="">.
Is there any way to get the first query to output the items in the same order as the list - or is the loop the only way to do this?
Thank you in advance.
December 15th, 2013, 08:57 PM
If there's no "logic" to how the SKUs should be ordered other than the order in the list, the only other option is to generate a custom order by clause. Loop over the SKUs and create an order by that looks like:
CASE WHEN products.pid = 'First SKU' THEN 1
WHEN products.pid = 'Second SKU' THEN 2
WHEN products.pid = 'Third SKU' THEN 3