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

Name	Type	Required	Description
objectName	string	Yes	Name of the object from which to select data
id	string	No	ID of a record to select
selectFields	array	No (default=[])	Array of field names to select. Can include relationships, e.g. ['tags.label as tag']
extraselectFields	array	No	Array 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
includeAllFormulaFields	boolean	No (default=false)	If true, all formula fields for the object will be added into the query
filter	any	No (default={})	Filter the records returned, see :ref:`preside-objects-filtering-data` in :doc:`/devguides/presideobjects`
filterParams	struct	No (default={})	Filter params for plain SQL filter, see :ref:`preside-objects-filtering-data` in :doc:`/devguides/presideobjects`
extraFilters	array	No (default=[])	An array of extra sets of filters. Each array should contain a structure with :code:`filter` and optional `code:`filterParams` keys.
savedFilters	array	No
orderBy	string	No (default="")	Plain SQL order by string
groupBy	string	No (default="")	Plain SQL group by string
autoGroupBy	boolean	No (default=false)	Whether or not to try to automatically calculate group by fields for the query
having	string	No (default="")	Plain SQL HAVING clause, can contain params that should be present in `filterParams` argument
maxRows	numeric	No (default=0)	Maximum number of rows to select
startRow	numeric	No (default=1)	Offset the recordset when using maxRows
useCache	boolean	No	Whether or not to automatically cache the result internally
fromVersionTable	boolean	No (default=false)	Whether or not to select the data from the version history table for the object
specificVersion	numeric	No (default=0)	Can be used to select a specific version when selecting from the version table
allowDraftVersions	boolean	No	Choose whether or not to allow selecting from draft records and/or versions
forceJoins	string	No (default="")	Can be set to "inner" / "left" to force all joins in the query to a particular join type
extraJoins	array	No (default=[])	An array of explicit joins to add to the query (can define subquery joins this way)
recordCountOnly	boolean	No (default=false)	If set to true, the method will just return the number of records that the select statement would return
getSqlAndParamsOnly	boolean	No (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
sqlAndParamsPrefix	string	No (default="")	If specified, all the params in the returned sql and params will be prefixed with the given string
formatSqlParams	boolean	No (default=false)	If set to true, params returned by `getSqlAndParamsOnly` will be in the format required by `selectData()`'s `filterParams`
distinct	boolean	No (default=false)	Whether or not the record set should be a 'distinct' select
tenantIds	struct	No (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.
bypassTenants	array	No (default=[])	Array of tenants to bypass. e.g. [ "site" ] to bypass site tenancy. See Configuring data tenancy for more information on tenancy.
ignoreDefaultFilters	array	No
returntype	string	No (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.
columnKey	string	No (default="")	When returntype="struct", "arrayOfValues" or "singleValue", this field is required to define the column that will be used for struct keys/values/value
timeout	numeric	No	Timeout, 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#%" }
);