queryRenameColumns()

queryRenameColumns.cfm

query function queryRenameColumns( required query query, required array columnNames, required array newColumnNames ){
	if( arguments.columnNames.Len() != arguments.newColumnNames.Len() )
		Throw( message: "Column name mismatch", detail: "The number of column names to change doesn't match the number of new names" );
	var queryJson = SerializeJSON( arguments.query );
	var columns = GetMetaData( arguments.query ).Map( function( item ){
		return item.name;
	});
	var newColumns = columns.Map( function( item ){
		var foundPosition = columnNames.FindNoCase( item );
		return foundPosition? newColumnNames[ foundPosition ]: item;
	});
	var columnsJson = SerializeJSON( columns );
	var newColumnsJson = SerializeJSON( newColumns );
	var queryJsonColumnRenamed = queryJson.Replace( 'COLUMNS":' & columnsJson, 'COLUMNS":' & newColumnsJson );
	return DeserializeJSON( queryJsonColumnRenamed, false );
}

queryRenameColumns.tests.cfm

describe( "queryRenameColumns", ()=> {

	beforeEach( ()=> {
		variables.q = QueryNew( "COL1,COL2,COL3", "VarChar,VarChar,VarChar", [ [ "COL1", "COL2", "COL3" ] ] );
	});

	it( "renames the specified columns in a query, preserving case", ()=> {
		actual = queryRenameColumns( q, [ "COL1", "COL3" ], [ "1st Column", "3rd Column" ] );
		actualColumns = GetMetadata( actual );
		expect( actualColumns[ 1 ].name ).toBeWithCase( "1st Column" );
		expect( actualColumns[ 2 ].name ).toBeWithCase( "COL2" );
		expect( actualColumns[ 3 ].name ).toBeWithCase( "3rd Column" );
		// check it's only changing the column name and not values
		expect( actual[ "COL2" ][ 1 ] ).toBe( "COL2" );
	});

	it( "throws an error if the number of column names to change doesn't match the number of new names", ()=> {
		expect( ()=> queryRenameColumns( q, [ "COL1", "COL3" ], [ "1st Column" ] ) )
			.toThrow( regex: "Column name mismatch" );
	});

});

queryRenameColumnsACFonly.cfm

/* This version perserves column types but only works on ACF. Lucee doesn't need it though: use query.setColumNames() and wait for https://luceeserver.atlassian.net/browse/LDEV-3208*/
query function queryRenameColumns( required query query, required array columnNames, required array newColumnNames ){
	if( arguments.columnNames.Len() != arguments.newColumnNames.Len() )
		Throw( message: "Column name mismatch", detail: "The number of column names to change doesn't match the number of new names" );
	// detect and store column types if present
	var queryColumns = GetMetaData( arguments.query );
	var columnTypes = [];
	if( queryColumns[ 1 ].KeyExists( "TypeName" ) ){
		for( var column in queryColumns ){
			columnTypes.Append( column.TypeName );
		}
	}
	var queryJson = SerializeJSON( arguments.query );
	var columns = GetMetaData( arguments.query ).Map( function( item ){
		return item.name;
	});
	var newColumns = columns.Map( function( item ){
		var foundPosition = columnNames.FindNoCase( item );
		return foundPosition? newColumnNames[ foundPosition ]: item;
	});
	var columnsJson = SerializeJSON( columns );
	var newColumnsJson = SerializeJSON( newColumns );
	var queryJsonColumnsRenamed = queryJson.Replace( 'COLUMNS":' & columnsJson, 'COLUMNS":' & newColumnsJson );
	var queryColumnsRenamed = DeserializeJSON( queryJsonColumnsRenamed, false );
	if( columnTypes.Len() ) queryColumnsRenamed.getMetaData().setColumnTypeNames( columnTypes );
	return queryColumnsRenamed;
}

queryRenameColumnsACFonly.tests.cfm

describe( "queryRenameColumns", function() {

	beforeEach( function() {
		variables.q = QueryNew( "COL1,COL2,COL3", "Integer,Double,VarChar", [ [ 1, 1.1, "string" ] ] );
	});

	it( "renames the specified columns in a query, preserving case and column types", function() {
		actual = queryRenameColumns( q, [ "COL1", "COL3" ], [ "1st Column", "3rd Column" ] );
		actualColumns = GetMetadata( actual );
		expect( actualColumns[ 1 ].name ).toBeWithCase( "1st Column" );
		expect( actualColumns[ 2 ].name ).toBeWithCase( "COL2" );
		expect( actualColumns[ 3 ].name ).toBeWithCase( "3rd Column" );
		// check it's only changing the column name and not values
		expect( actual[ "COL2" ][ 1 ] ).toBe( 1.1 );
		// check types are preserved
		expect( actualColumns[ 1 ].typeName ).toBe( "INTEGER" );
		expect( actualColumns[ 2 ].typeName ).toBe( "DOUBLE" );
		expect( actualColumns[ 3 ].typeName ).toBe( "VARCHAR" );
	});

	it( "throws an error if the number of column names to change doesn't match the number of new names", function() {
		expect( function(){
			queryRenameColumns( q, [ "COL1", "COL3" ], [ "1st Column" ] );
		}).toThrow( regex="Column name mismatch" );
	});

});