scs-model-utility

Model Utility

Model Utility is a ColdBox module that helps you build dynamic SQL queries from a safe, allow-listed filter configuration.

It includes:

• A legacy helper: createQueryFilters()
• An advanced helper: queryHelper()
• Date range utilities: presetDateRange()

Installation

Install with CommandBox:

box install model-utility

Prerequisites

• ColdBox application
• Familiarity with ColdFusion/CFML query building and queryExecute()

Injecting the Model

Inject ModelUtility from this module into your handler, service, or model:

property name="modelUtility" inject="ModelUtility@model-utility";

Query Configuration Structure (what you send and what you receive to the Advanced Helper function)

Parameters it can accept {struct}:

var queryConfig = {
			'allowedFilters'  : {},
			'providedFilters' : {},
			'extra'           : {
				'dateRange'         : {},
				'filtersApproved'   : [],
				'filtersRejected'   : [],
				'sqlClauses'        : {},
				'sqlSpecialClauses' : {},
				'specialFilters'    : {}
			},
			'autoExecute' : false,
			'sqlQuery'    : {
				'base'           : '',
				'select'         : '',
				'from'           : '',
				'join'           : '',
				'where'          : '',
				'groupBy'        : '',
				'having'         : '',
				'specialClauses' : '',
				'orderBy'        : ''
			},
			'dateRange' : {},
			'options'   : {}
		};

var query = modelUtility.queryHelper( queryConfig );

Expected structure to receive {struct}

<!--- 
{
    'sql'     : '',
    'params'  : {},
    'options' : {},
    'extra'   : {}
} 
--->

Quick Start

Basic Example:

    var query = modelUtility.queryHelper( {
        'dateRange'       : arguments.dateRange,
        'allowedFilters'  : variables.allowedFilters,
        'providedFilters' : arguments.filters
    } );

writeDump( var="#query#", label="query", abort=true);

Advanced Example:

    var query = modelUtility.queryHelper( {
        'dateRange' : {
            'preset' : 'this_year'
        },
        'sqlQuery'  : {
         	'select'    : 'SELECT *',
         	'from'    : 'FROM dual',
         	'where'   : 'WHERE 1=1',
            'specialClauses' : 'AND 1<>0',
            'orderBy' : 'ORDER BY 1'
        },
        'allowedFilters'  : variables.allowedFilters,
        'providedFilters' : arguments.filters
    } );

writeDump( var="#query#", label="query", abort=true);    

API Reference

createQueryFilters( allowedFilters, providedFilters, specialFilters={} )

Legacy helper that returns SQL snippets and parameter definitions.

Inputs

• allowedFilters (struct): allow-list of accepted filter names and metadata.
• providedFilters (struct): incoming filters (for example from request params).
• specialFilters (optional struct): additional SQL fragments keyed by name.

Each allowedFilters[filterName] entry should include:

• columnName (string)
• cfsqltype (string)
• list (boolean)

Return

{
	params = {
		filterName = {
			value = "...",
			cfsqltype = "cf_sql_varchar",
			list = true
		}
	},
	sql = " AND some_column IN (:filterName)"
}

Example

var queryFilters = modelUtility.createQueryFilters(
    allowedFilters  = variables.allowedFilters,
    providedFilters = arguments.filters
);

var sql = "SELECT u.* FROM users u WHERE 1=1" & queryFilters.sql;
 var data = queryExecute( sql, queryFilters.params, { datasource = application.dsn } );

queryHelper( queryConfig )

Advanced helper that standardizes filter parsing, date handling, options, and SQL assembly.

Core queryConfig keys

• allowedFilters (struct)
• providedFilters (struct)
• dateRange (struct)
• specialFilters (array or struct)
• sqlQuery (struct): SQL pieces (base, select, from, join, where, groupBy, having, specialClauses, orderBy)
• options (struct): datasource, cachedWithin, returnType, name
• autoExecute (boolean): when true and datasource is present, runs queryExecute() and returns the query result

Configuration example

• Most common and Basic Example:

var query = modelUtility.queryHelper( {
	'dateRange'       : arguments.dateRange,
    'allowedFilters'  : variables.allowedFilters,
    'providedFilters' : arguments.filters
} );

<!--- 
You could use the following or its equivalent
return queryExecute(
    query.sql,
    query.params,
    query.options
);
--->

Example: Auto execute query

var queryConfig = {
    'autoExecute' : true,
    'options'     : {
        'datasource' : dsn.name,
        'returntype' : 'array'
    },
    'sqlQuery' : {
        'select'         : 'SELECT * FROM dual WHERE 1=1',
        'specialClauses' : 'AND 1<>0',
        'orderBy'        : 'ORDER BY 1'
    }
};

var query = modelUtility.queryHelper( queryConfig );

writeDump( var="#query#", label="query", abort=true); 

Notes:

• queryHelper() only auto-executes when autoExecute=true and options.datasource is provided.
• Use extra.filtersRejected to detect and log blocked filter keys.

Date Presets

presetDateRange() supports: Asuming today is August 6th 2026 the preset will return:

• today : {ts '2026-08-06 00:00: 00'} to {ts '2026-08-06 23:59: 59'}

• yesterday : {ts '2026-08-05 00:00: 00'} to {ts '2026-08-05 23:59: 59'}

• this_week : {ts '2026-08-03 00:00: 00'} to {ts '2026-08-09 23:59: 59'}

• this_month : {ts '2026-08-01 00:00: 00'} to {ts '2026-08-31 23:59: 59'}

• this_quarter : {ts '2026-07-01 00:00: 00'} to {ts '2026-09-30 23:59: 59'}

• this_halfyear :{ts '2026-07-01 00:00: 00'} to {ts '2026-12-31 23:59: 59'}

• this_year : {ts '2026-01-01 00:00: 00'} to {ts '2026-12-31 23:59: 59'}

• last_week : {ts '2026-07-27 00:00: 00'} to {ts '2026-08-02 23:59: 59'}

• last_month : {ts '2026-07-01 00:00: 00'} to {ts '2026-07-31 23:59: 59'}

• last_quarter : {ts '2026-04-01 00:00: 00'} to {ts '2026-06-30 23:59: 59'}

• last_halfyear : {ts '2026-01-01 00:00: 00'} to {ts '2026-06-30 23:59: 59'}

• last_year : {ts '2025-01-01 00:00: 00'} to {ts '2025-12-31 23:59: 59'}

• last_7_days : {ts '2026-07-30 00:00: 00'} to {ts '2026-08-05 23:59: 59'}

• last_30_days : {ts '2026-07-07 00:00: 00'} to {ts '2026-08-05 23:59: 59'}

• last_60_days : {ts '2026-06-07 00:00: 00'} to {ts '2026-08-05 23:59: 59'}

• last_90_days : {ts '2026-05-08 00:00: 00'} to {ts '2026-08-05 23:59: 59'}

• last_180_days : {ts '2026-02-07 00:00: 00'} to {ts '2026-08-05 23:59: 59'}

• last_365_days : {ts '2025-08-06 00:00: 00'} to {ts '2026-08-05 23:59: 59'}

Example:

    var thisWeek = modelUtility.presetDateRange( 'this_week' )

    writeDump(
        var = '#thisWeek#',
        label = 'thisWeek',
        abort = true
    );

<!--- 
    { 
        'start' : {ts '2026-08-03 00:00:                   00'},
        'end' : {ts '2026-08-09 23:59:                   59'}
    }
---->

Error Handling

Invalid date range inputs throw ModelUtility.InvalidDateRange.

Best Practices

• Always define filter allow-lists in allowedFilters; never map user keys directly into SQL.
• Keep SQL templates in sqlQuery and parameters in providedFilters.
• Use list-based binding (list=true) for IN (:param) style filters.
• Log extra.filtersRejected for security visibility.

Authors & Contributors

• Eduardo Gomez - @egomezm

References

• ColdBox Platform