selectData()

Overview

public any function selectData(
      required string  objectName             
    ,          string  id                     
    ,          array   selectFields            = []
    ,          array   extraselectFields      
    ,          boolean includeAllFormulaFields = false
    ,          any     filter                  = {}
    ,          struct  filterParams            = {}
    ,          array   extraFilters            = []
    ,          array   savedFilters           
    ,          string  orderBy                 = ""
    ,          string  groupBy                 = ""
    ,          boolean autoGroupBy             = false
    ,          string  having                  = ""
    ,          numeric maxRows                 = 0
    ,          numeric startRow                = 1
    ,          boolean useCache               
    ,          boolean fromVersionTable        = false
    ,          numeric specificVersion         = 0
    ,          boolean allowDraftVersions     
    ,          string  forceJoins              = ""
    ,          array   extraJoins              = []
    ,          boolean recordCountOnly         = false
    ,          boolean getSqlAndParamsOnly     = false
    ,          string  sqlAndParamsPrefix      = ""
    ,          boolean formatSqlParams         = false
    ,          boolean distinct                = false
    ,          struct  tenantIds               = {}
    ,          array   bypassTenants           = []
    ,          array   ignoreDefaultFilters   
    ,          string  returntype              = "query"
    ,          string  columnKey               = ""
    ,          numeric timeout                
)

Selects database records for the given object based on a variety of input parameters

Arguments

NameTypeRequiredDescription
objectNamestringYesName of the object from which to select data
idstringNoID of a record to select
selectFieldsarrayNo (default=[])Array of field names to select. Can include relationships, e.g. ['tags.label as tag']
extraselectFieldsarrayNoArray of field names to select in addition to `selectFields`. Can include relationships, e.g. ['tags.label as tag']. Use this if you want specific extra fields (e.g. formula fields) in addition to selecting all physical fields
includeAllFormulaFieldsbooleanNo (default=false)If true, all formula fields for the object will be added into the query
filteranyNo (default={})Filter the records returned, see :ref:`preside-objects-filtering-data` in :doc:`/devguides/presideobjects`
filterParamsstructNo (default={})Filter params for plain SQL filter, see :ref:`preside-objects-filtering-data` in :doc:`/devguides/presideobjects`
extraFiltersarrayNo (default=[])An array of extra sets of filters. Each array should contain a structure with :code:`filter` and optional `code:`filterParams` keys.
savedFiltersarrayNo
orderBystringNo (default="")Plain SQL order by string
groupBystringNo (default="")Plain SQL group by string
autoGroupBybooleanNo (default=false)Whether or not to try to automatically calculate group by fields for the query
havingstringNo (default="")Plain SQL HAVING clause, can contain params that should be present in `filterParams` argument
maxRowsnumericNo (default=0)Maximum number of rows to select
startRownumericNo (default=1)Offset the recordset when using maxRows
useCachebooleanNoWhether or not to automatically cache the result internally
fromVersionTablebooleanNo (default=false)Whether or not to select the data from the version history table for the object
specificVersionnumericNo (default=0)Can be used to select a specific version when selecting from the version table
allowDraftVersionsbooleanNoChoose whether or not to allow selecting from draft records and/or versions
forceJoinsstringNo (default="")Can be set to "inner" / "left" to force *all* joins in the query to a particular join type
extraJoinsarrayNo (default=[])An array of explicit joins to add to the query (can define subquery joins this way)
recordCountOnlybooleanNo (default=false)If set to true, the method will just return the number of records that the select statement would return
getSqlAndParamsOnlybooleanNo (default=false)If set to true, the method will not execute any query. Instead it will just return a struct with a `sql` key containing the plain string SQL that would have been executed and a `params` key with an array of params that would be included
sqlAndParamsPrefixstringNo (default="")If specified, all the params in the returned sql and params will be prefixed with the given string
formatSqlParamsbooleanNo (default=false)If set to true, params returned by `getSqlAndParamsOnly` will be in the format required by `selectData()`'s `filterParams`
distinctbooleanNo (default=false)Whether or not the record set should be a 'distinct' select
tenantIdsstructNo (default={})Struct of tenant IDs. Keys of the struct indicate the tenant, values indicate the ID. e.g. `{ site=specificSiteId }`. These values will override the current active tenant for the request.
bypassTenantsarrayNo (default=[])Array of tenants to bypass. e.g. [ "site" ] to bypass site tenancy. See Configuring data tenancy for more information on tenancy.
ignoreDefaultFiltersarrayNo
returntypestringNo (default="query")Either query (default),array,struct,arrayOfValues,singleRecordStruct or singleValue. Array and struct correspond to https://docs.lucee.org/reference/tags/query.html#attribute-returntype. ArrayOfValues return column array of the specified column in columnKey. SingleRecordStruct returns the first record in the result as a struct. SingleValue returns first result value of the specified column.
columnKeystringNo (default="")When returntype="struct", "arrayOfValues" or "singleValue", this field is required to define the column that will be used for struct keys/values/value
timeoutnumericNoTimeout, in seconds, of the main select DB query

Examples

// select a record by ID
event = presideObjectService.selectData( objectName="event", id=rc.id );


// select records using a simple filter.
// notice the 'category.label as categoryName' field - this will
// be automatically selected from the related 'category' object
events = presideObjectService.selectData(
          objectName   = "event"
        , filter       = { category = rc.category }
        , selectFields = [ "event.name", "category.label as categoryName", "event.category" ]
        , orderby      = "event.name"
);


// select records with a plain SQL filter with added SQL params
events = presideObjectService.selectData(
          objectName   = "event"
        , filter       = "category.label like :category.label"
        , filterParams = { "category.label" = "%#rc.search#%" }
);