Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
queryRenameColumns()
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 );
}
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" );
});
});
/* 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;
}
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" );
});
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment