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#%" }
);