ColdBox Platform Version 2.6.3

coldbox.system.plugins
Class queryHelper

WEB-INF.cftags.component
        extended by coldbox.system.frameworkSupertype
            extended by coldbox.system.plugin
                extended by coldbox.system.plugins.queryHelper

public class queryHelper
extends plugin

A query helper plugin.


Constructor Summary
init(any controller)
 
Method Summary
private query QrySetCell(query qryFrom, query qryTo, array ArrayCols, numeric FromRowNumber, numeric ToRowNumber)
          Insert value into query
 query doInnerJoin(query qryLeft, query qryRight, string LeftJoinColumn, string RightJoinColumn, [string OrderByElement=''], [boolean CaseSensitive='false'])
          Return inner-joined Query
 query doLeftOuterJoin(query qryLeft, query qryRight, string LeftJoinColumn, string RightJoinColumn, [string OrderByElement=''], [boolean CaseSensitive='false'])
          Return left outer-joined Query
 query doQueryAppend(query qryFrom, query qryTo)
          Append Query1 into Query2
 query filterNull(query qry, string field, [string null='NULL'])
          Filters a query by NULL
 query filterQuery(query qry, string field, string value, [string cfsqltype='cf_sql_varchar'], [boolean list='false'])
          Filters a query by the given value
 any getColumnArray(query qry, string ColumnName)
          Returns an array of the values
 numeric getCountDistinct(query qry, string ColumnName)
          Returns total/count disninct values
 numeric getRowNumber(query qry, string ColumnValue, string ColumnName)
          Returns the row number of the first match
private string getUnMatchedElements(string FirstList, string secondList)
          Returns element which are only present in second-list
private any getUniqueElements(string FirstList, string secondList)
          Returns unique elements from two list
 query sortQuery(query qry, string sortBy, [string sortOrder='ASC'])
          Sorts a query by the given field
 
Methods inherited from class coldbox.system.plugin
getPluginDescription, getPluginName, getPluginVersion, getpluginPath, setPluginDescription, setPluginName, setPluginVersion
 
Methods inherited from class coldbox.system.frameworkSupertype
abort, announceInterception, dump, getColdboxOCM, getDatasource, getDebugMode, getHash, getInstance, getInterceptor, getMailSettings, getModel, getMyPlugin, getPlugin, getResource, getSetting, getSettingStructure, getSettingsBean, getcontroller, getfwLocale, htmlhead, include, includeUDF, locateDirectoryPath, locateFilePath, persistVariables, populateModel, relocate, renderExternalView, renderView, rethrowit, runEvent, setDebugMode, setNextEvent, setNextRoute, setSetting, setcontroller, settingExists, throw
 
Methods inherited from class WEB-INF.cftags.component
 

Constructor Detail

init

public init(any controller)

Method Detail

QrySetCell

private query QrySetCell(query qryFrom, query qryTo, array ArrayCols, numeric FromRowNumber, numeric ToRowNumber)
Insert value into query

Parameters:
qryFrom
qryTo
ArrayCols
FromRowNumber
ToRowNumber

doInnerJoin

public query doInnerJoin(query qryLeft, query qryRight, string LeftJoinColumn, string RightJoinColumn, [string OrderByElement=''], [boolean CaseSensitive='false'])
Return inner-joined Query

Parameters:
qryLeft
qryRight
LeftJoinColumn - the column name, not the value of column
RightJoinColumn - the column name, not the value of column
OrderByElement
CaseSensitive

doLeftOuterJoin

public query doLeftOuterJoin(query qryLeft, query qryRight, string LeftJoinColumn, string RightJoinColumn, [string OrderByElement=''], [boolean CaseSensitive='false'])
Return left outer-joined Query

Parameters:
qryLeft
qryRight
LeftJoinColumn - the column name, not the value of column
RightJoinColumn - the column name, not the value of column
OrderByElement
CaseSensitive

doQueryAppend

public query doQueryAppend(query qryFrom, query qryTo)
Append Query1 into Query2

Parameters:
qryFrom - Append Query1 into Query2
qryTo - Query2 will have all record from Query1

filterNull

public query filterNull(query qry, string field, [string null='NULL'])
Filters a query by NULL

Parameters:
qry - Query to filter
field - Field to filter on
null - NULL by default, also accepts NOT NULL

filterQuery

public query filterQuery(query qry, string field, string value, [string cfsqltype='cf_sql_varchar'], [boolean list='false'])
Filters a query by the given value

Parameters:
qry - Query to filter
field - Field to filter on
value - Value to filter on
cfsqltype - The cf sql type of the value.
list - Whether to do a where IN list.

getColumnArray

public any getColumnArray(query qry, string ColumnName)
Returns an array of the values

Parameters:
qry - cf query
ColumnName - column name

getCountDistinct

public numeric getCountDistinct(query qry, string ColumnName)
Returns total/count disninct values

Parameters:
qry - cf query
ColumnName - column/s name

getRowNumber

public numeric getRowNumber(query qry, string ColumnValue, string ColumnName)
Returns the row number of the first match

Parameters:
qry - cf query
ColumnValue - column value
ColumnName - column name

getUnMatchedElements

private string getUnMatchedElements(string FirstList, string secondList)
Returns element which are only present in second-list

Parameters:
FirstList - first list which be compared to second list
secondList - second list which be compared from first list

getUniqueElements

private any getUniqueElements(string FirstList, string secondList)
Returns unique elements from two list

Parameters:
FirstList - first list which be compared to second list
secondList - second list which be compared from first list

sortQuery

public query sortQuery(query qry, string sortBy, [string sortOrder='ASC'])
Sorts a query by the given field

Parameters:
qry - Query to sort
sortBy - Sort by column(s)
sortOrder - ASC/DESC

ColdBox Platform Version 2.6.3