ColdBox Platform Version 3.7.0

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
 any arrayOfStructuresToQuery(array theArray)
          Converts an array of structures to a CF Query Object
 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
 string getCSV(query qry, [string delimiter=','])
          returns query in delimited text file format
 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
private query qrySetCell(query qryFrom, query qryTo, array ArrayCols, numeric FromRowNumber, numeric ToRowNumber)
          Insert value into query
 query querySim(string queryData)
          First line are the query columns separated by commas
 array queryToArrayOfStructures(query theQuery)
          Converts a query to an array of structures
 Query rotateQuery(query originalQuery)
          Rotates query swapping rows for cols and cols for rows, first col becomes new col names
 string slugifyCol(string str, [numeric maxLength='0'], [string allow=''])
          Create a query column name safe slug from a string
 query sortQuery(query qry, string sortBy, [string sortOrder='ASC'])
          Sorts a query by the given field
 query sortQueryNoCase(query qry, string sortBy, [string sortOrder='ASC'])
          Sorts a query by the given field non-case
 
Methods inherited from class coldbox.system.Plugin
getPluginDescription, getPluginName, getPluginVersion, getRequestCollection, getRequestContext, getpluginAuthor, getpluginAuthorURL, getpluginPath, setPluginDescription, setPluginName, setPluginVersion, setpluginAuthor, setpluginAuthorURL
 
Methods inherited from class coldbox.system.FrameworkSupertype
$abort, $dump, $htmlhead, $include, $rethrow, $throw, addAsset, announceInterception, getColdboxOCM, getController, getDatasource, getDebugMode, getInterceptor, getMailService, getMailSettings, getMemento, getModel, getModuleSettings, getMyPlugin, getNewMail, getPlugin, getResource, getSetting, getSettingStructure, getSettingsBean, getValidationManager, getfwLocale, includeUDF, loadGlobalUDFLibraries, locateDirectoryPath, locateFilePath, persistVariables, populateModel, relocate, renderExternalView, renderLayout, renderView, runEvent, setDebugMode, setNextEvent, setNextRoute, setSetting, setfwLocale, settingExists, validateModel
 
Methods inherited from class WEB-INF.cftags.component
 

Constructor Detail

init

public init(any controller)

Parameters:
controller
Method Detail

arrayOfStructuresToQuery

public any arrayOfStructuresToQuery(array theArray)
Converts an array of structures to a CF Query Object.

Parameters:
theArray - The array of structures to convert to a query

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.

getCSV

public string getCSV(query qry, [string delimiter=','])
returns query in delimited text file format

Parameters:
qry - query to return as a delimited text file
delimiter - delimiter

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

qrySetCell

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

Parameters:
qryFrom
qryTo
ArrayCols
FromRowNumber
ToRowNumber

querySim

public query querySim(string queryData)
First line are the query columns separated by commas. Then do a consecuent rows separated by line breaks separated by | to denote columns.

Parameters:
queryData - The data to create queries

queryToArrayOfStructures

public array queryToArrayOfStructures(query theQuery)
Converts a query to an array of structures

Parameters:
theQuery - The query to convert

rotateQuery

public Query rotateQuery(query originalQuery)
Rotates query swapping rows for cols and cols for rows, first col becomes new col names

Parameters:
originalQuery - The query to rotate

slugifyCol

public string slugifyCol(string str, [numeric maxLength='0'], [string allow=''])
Create a query column name safe slug from a string

Parameters:
str - The string to slugify
maxLength - The maximum number of characters for the slug
allow - a regex safe list of additional characters to allow

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

sortQueryNoCase

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

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

ColdBox Platform Version 3.7.0