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