CFML UDF to reorders query rows based on an explicit list of ID values - ColdFusion / Lucee

queryReorder.cfm

<cfscript>
/**
 * Reorders query rows based on an explicit list of ID values
 * Uses a temporary sort column and Query-of-Queries for reordering
 * 2025-12-05
 * v1
 * James Moberg - MyCFML.com https://www.myCFML.com/
 * Sponsored by https://www.SunStarMedia.com/
 * @param queryData The query to reorder
 * @param idColumn The name of the ID column to match against
 * @param idOrder An array of IDs in desired order
 * @return Query with rows reordered according to idList array
 */
query function queryReorder(required query queryData, required string idColumn, required array idOrder) {
	// Empty? return original query
	if (!arguments.queryData.recordCount || !arraylen(arguments.idOrder)) {
		return arguments.queryData;
	}
	// clone the query so it doesn't modify the original calling script's query
	local.qry = duplicate(arguments.queryData);

	// Add the sort column with incremented default values
	local.sortColumn = "NewSortOrder";
	local.defaultSortValue = arraylen(arguments.idOrder) + 1;
	queryaddcolumn(local.qry, local.sortColumn, "integer", []);
	for (local.row in local.qry) {
		local.qry[local.sortColumn][local.qry.currentrow] = arrayfindnocase(arguments.idOrder, local.row[arguments.idColumn]) ? javacast("int", arrayfindnocase(arguments.idOrder, local.row[arguments.idColumn])) : javacast("int", local.defaultSortValue);
		local.defaultSortValue++;
	}

	// identify column names and retain original column order & name case. Remove temp sort column.
	local.cols = (structkeyexists(server, "lucee")) ? local.qry.getColumnNames() : local.qry.getMetaData().getColumnLabels();
	local.cols = arraynew(1).append(local.cols, true); // convert java array back to CF array
	local.colPos = arrayfindnocase(local.cols, local.sortColumn);
	if (local.colPos){
		arraydeleteat(local.cols, local.colPos);
	}

	return queryexecute("SELECT #arraytolist(local.cols)# FROM [local].qry ORDER BY #local.sortColumn#", {}, {"dbtype": "query"});
}
</cfscript>