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
Class Attributes:
  • cache : true
    •  
  • output : false
    •  

    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
    None
    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