CRM_Utils_SQL_Select
extends CRM_Utils_SQL_BaseParamQuery
in package
Object-oriented SQL builder for SELECT queries.
This class is foundational to CiviCRM's query functionality for the API, SearchKit, ScheduledReminders, MailingRecipients, etc.
Usage:
$select = CRM_Utils_SQL_Select::from('civicrm_activity act')
->join('absence', 'inner join civicrm_activity absence on absence.id = act.source_record_id')
->where('activity_type_id = #type', array('type' => 234))
->where('status_id IN (#statuses)', array('statuses' => array(1,2,3))
->where('subject like @subj', array('subj' => '%hello%'))
->where('!dynamicColumn = 1', array('dynamicColumn' => 'coalesce(is_active,0)'))
->where('!column = @value', array(
'column' => $customField->column_name,
'value' => $form['foo']
))
echo $select->toSQL();
Design principles:
- Portable
- No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
- No knowledge of the underlying data model
- SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
- Variable escaping is concise and controllable based on prefixes, eg
- similar to Drupal's t()
- use "@varname" to insert the escaped value
- use "!varname" to insert raw (unescaped) values
- use "#varname" to insert a numerical value (these are validated but not escaped)
- to disable any preprocessing, simply omit the variable list
- control characters (@!#) are mandatory in expressions but optional in arg-keys
- Variables may be individual values or arrays; arrays are imploded with commas
- Conditionals are AND'd; if you need OR's, do it yourself
- Use classes/functions with documentation (rather than undocumented array-trees)
- For any given string, interpolation is only performed once. After an interpolation, a string may never again be subjected to interpolation.
The "interpolate-once" principle can be enforced by either interpolating on input xor output. The notations for input and output interpolation are a bit different, and they may not be mixed.
// Interpolate on input. Set params when using them.
$select->where('activity_type_id = #type', array(
'type' => 234,
));
// Interpolate on output. Set params independently.
$select
->where('activity_type_id = #type')
->param('type', 234),
Tags
Table of Contents
Constants
- INTERPOLATE_AUTO = 'auto'
- Determine mode automatically. When the first attempt is made to use input-interpolation (eg `where(..., array(...))`) or output-interpolation (eg `param(...)`), the mode will be set. Subsequent calls will be validated using the same mode.
- INTERPOLATE_INPUT = 'in'
- Interpolate values as soon as they are passed in (where(), join(), etc).
- INTERPOLATE_OUTPUT = 'out'
- Interpolate values when rendering SQL output (toSQL()).
Properties
- $strict : bool
- Public to work-around PHP 5.3 limit.
- $allowLiterals : mixed
- $mode : mixed
- $params : array<string|int, mixed>
- $distinct : mixed
- $from : mixed
- $groupBys : mixed
- $havings : mixed
- $insertInto : mixed
- $insertIntoFields : mixed
- $insertVerb : mixed
- $joins : mixed
- $limit : mixed
- $offset : mixed
- $onDuplicates : mixed
- $orderBys : mixed
- $selects : mixed
- $setAlias : mixed
- $setOps : mixed
- $wheres : mixed
Methods
- __construct() : mixed
- Create a new SELECT query.
- allowLiterals() : mixed
- copy() : CRM_Utils_SQL_Select
- Make a new copy of this query.
- distinct() : CRM_Utils_SQL_Select
- Return only distinct values
- escapeString() : string
- execute() : CRM_Core_DAO
- Execute the query.
- fragment() : CRM_Utils_SQL_Select
- Create a partial SELECT query.
- from() : CRM_Utils_SQL_Select
- Create a new SELECT query.
- fromSet() : CRM_Utils_SQL_Select
- Create a new SELECT-like query by performing set-operations (e.g. UNION).
- getFrom() : string
- getWhere() : array<string|int, mixed>
- groupBy() : CRM_Utils_SQL_Select
- Group results by adding extra items to the GROUP BY clause.
- having() : CRM_Utils_SQL_Select
- Limit results by adding extra condition(s) to the HAVING clause
- insertIgnoreInto() : CRM_Utils_SQL_Select
- Wrapper function of insertInto fn but sets insertVerb = "INSERT IGNORE INTO "
- insertInto() : CRM_Utils_SQL_Select
- Insert the results of the SELECT query into another table.
- insertIntoField() : CRM_Utils_SQL_Select
- interpolate() : string
- Given a string like "field_name = @value", replace "@value" with an escaped SQL string
- isEmpty() : bool
- join() : CRM_Utils_SQL_Select
- Add a new JOIN clause.
- limit() : CRM_Utils_SQL_Select
- Set a limit on the number of records to return.
- merge() : CRM_Utils_SQL_Select
- Merge something or other.
- offsetExists() : bool
- Has an offset been set.
- offsetGet() : mixed
- Get the value of a SQL parameter.
- offsetSet() : void
- Set the value of a SQL parameter.
- offsetUnset() : void
- Unset the value of a SQL parameter.
- onDuplicate() : CRM_Utils_SQL_Select
- For INSERT INTO...SELECT...' queries, you may give an "ON DUPLICATE UPDATE" clause.
- orderBy() : CRM_Utils_SQL_Select
- Sort results by adding extra items to the ORDER BY clause.
- param() : CRM_Utils_SQL_Select
- Set one (or multiple) parameters to interpolate into the query.
- replaceInto() : mixed
- Wrapper function of insertInto fn but sets insertVerb = "REPLACE INTO "
- select() : CRM_Utils_SQL_Select
- Specify the column(s)/value(s) to return by adding to the SELECT clause
- setOp() : $this
- Add a set operation.
- strict() : self
- Enable (or disable) strict mode.
- syncInto() : $this
- Take the results of the SELECT query and copy them into another table.
- toSQL() : string
- union() : $this
- Add a union to the list of set operations.
- where() : CRM_Utils_SQL_Select
- Limit results by adding extra condition(s) to the WHERE clause
Constants
INTERPOLATE_AUTO
Determine mode automatically. When the first attempt is made to use input-interpolation (eg `where(..., array(...))`) or output-interpolation (eg `param(...)`), the mode will be set. Subsequent calls will be validated using the same mode.
public
mixed
INTERPOLATE_AUTO
= 'auto'
INTERPOLATE_INPUT
Interpolate values as soon as they are passed in (where(), join(), etc).
public
mixed
INTERPOLATE_INPUT
= 'in'
Default.
Pro: Every clause has its own unique namespace for parameters. Con: Probably slower. Advice: Use this when aggregating SQL fragments from agents who maintained by different parties.
INTERPOLATE_OUTPUT
Interpolate values when rendering SQL output (toSQL()).
public
mixed
INTERPOLATE_OUTPUT
= 'out'
Pro: Probably faster. Con: Must maintain an aggregated list of all parameters. Advice: Use this when you have control over the entire query.
Properties
$strict
Public to work-around PHP 5.3 limit.
public
bool
$strict
= \NULL
$allowLiterals
protected
mixed
$allowLiterals
= \FALSE
$mode
protected
mixed
$mode
= \NULL
$params
protected
array<string|int, mixed>
$params
= []
$distinct
private
mixed
$distinct
= \NULL
$from
private
mixed
$from
$groupBys
private
mixed
$groupBys
= []
$havings
private
mixed
$havings
= []
$insertInto
private
mixed
$insertInto
= \NULL
$insertIntoFields
private
mixed
$insertIntoFields
= []
$insertVerb
private
mixed
$insertVerb
= 'INSERT INTO '
$joins
private
mixed
$joins
= []
$limit
private
mixed
$limit
= \NULL
$offset
private
mixed
$offset
= \NULL
$onDuplicates
private
mixed
$onDuplicates
= []
$orderBys
private
mixed
$orderBys
= []
$selects
private
mixed
$selects
= []
$setAlias
private
mixed
$setAlias
$setOps
private
mixed
$setOps
$wheres
private
mixed
$wheres
= []
Methods
__construct()
Create a new SELECT query.
public
__construct(string $from[, array<string|int, mixed> $options = [] ]) : mixed
Parameters
- $from : string
-
Table-name and optional alias.
- $options : array<string|int, mixed> = []
allowLiterals()
public
allowLiterals([bool $allowLiterals = TRUE ]) : mixed
Parameters
- $allowLiterals : bool = TRUE
copy()
Make a new copy of this query.
public
copy() : CRM_Utils_SQL_Select
Return values
CRM_Utils_SQL_Selectdistinct()
Return only distinct values
public
distinct([bool $isDistinct = TRUE ]) : CRM_Utils_SQL_Select
Parameters
- $isDistinct : bool = TRUE
-
allow DISTINCT select or not
Return values
CRM_Utils_SQL_SelectescapeString()
public
escapeString(string|null $value) : string
Parameters
- $value : string|null
Return values
string —SQL expression, e.g. "it's great" (with-quotes) or NULL (without-quotes)
execute()
Execute the query.
public
execute([string|null $daoName = NULL ][, bool $i18nRewrite = TRUE ]) : CRM_Core_DAO
To examine the results, use a function like fetch(), fetchAll(),
fetchValue(), or fetchMap().
Parameters
- $daoName : string|null = NULL
-
The return object should be an instance of this class. Ex: 'CRM_Contact_BAO_Contact'.
- $i18nRewrite : bool = TRUE
-
If the system has multilingual features, should the field/table names be rewritten?
Tags
Return values
CRM_Core_DAOfragment()
Create a partial SELECT query.
public
static fragment([array<string|int, mixed> $options = [] ]) : CRM_Utils_SQL_Select
Parameters
- $options : array<string|int, mixed> = []
Return values
CRM_Utils_SQL_Selectfrom()
Create a new SELECT query.
public
static from(string $from[, array<string|int, mixed> $options = [] ]) : CRM_Utils_SQL_Select
Parameters
- $from : string
-
Table-name and optional alias.
- $options : array<string|int, mixed> = []
Return values
CRM_Utils_SQL_SelectfromSet()
Create a new SELECT-like query by performing set-operations (e.g. UNION).
public
static fromSet([array<string|int, mixed> $options = [] ]) : CRM_Utils_SQL_Select
For example, if you want to query two tables and treat the results as one combined-set, then this is s a set-operation.
$queryA = CRM_Utils_SQL_Select::from('table_a'); $queryB = CRM_Utils_SQL_Select::from('table_b'); $querySet = CRM_Utils_SQL_Select::fromSet()->union('DISTINCT', [$queryA, $queryB])->toSQL();
Parameters
- $options : array<string|int, mixed> = []
-
Ex: ['setAlias' => 'uniondata']
Return values
CRM_Utils_SQL_SelectgetFrom()
public
getFrom() : string
Return values
stringgetWhere()
public
getWhere() : array<string|int, mixed>
Return values
array<string|int, mixed>groupBy()
Group results by adding extra items to the GROUP BY clause.
public
groupBy(string|array<string|int, mixed> $exprs[, null|array<string|int, mixed> $args = NULL ]) : CRM_Utils_SQL_Select
Parameters
- $exprs : string|array<string|int, mixed>
-
list of SQL expressions
- $args : null|array<string|int, mixed> = NULL
-
use NULL to disable interpolation; use an array of variables to enable
Return values
CRM_Utils_SQL_Selecthaving()
Limit results by adding extra condition(s) to the HAVING clause
public
having(string|array<string|int, mixed> $exprs[, null|array<string|int, mixed> $args = NULL ]) : CRM_Utils_SQL_Select
Parameters
- $exprs : string|array<string|int, mixed>
-
list of SQL expressions
- $args : null|array<string|int, mixed> = NULL
-
use NULL to disable interpolation; use an array of variables to enable
Return values
CRM_Utils_SQL_SelectinsertIgnoreInto()
Wrapper function of insertInto fn but sets insertVerb = "INSERT IGNORE INTO "
public
insertIgnoreInto(string $table[, array<string|int, mixed> $fields = [] ]) : CRM_Utils_SQL_Select
Parameters
- $table : string
-
The name of the other table (which receives new data).
- $fields : array<string|int, mixed> = []
-
The fields to fill in the other table (in order).
Return values
CRM_Utils_SQL_SelectinsertInto()
Insert the results of the SELECT query into another table.
public
insertInto(string $table[, array<string|int, mixed> $fields = [] ]) : CRM_Utils_SQL_Select
Parameters
- $table : string
-
The name of the other table (which receives new data).
- $fields : array<string|int, mixed> = []
-
The fields to fill in the other table (in order).
Tags
Return values
CRM_Utils_SQL_SelectinsertIntoField()
public
insertIntoField(array<string|int, mixed> $fields) : CRM_Utils_SQL_Select
Parameters
- $fields : array<string|int, mixed>
-
The fields to fill in the other table (in order).
Return values
CRM_Utils_SQL_Selectinterpolate()
Given a string like "field_name = @value", replace "@value" with an escaped SQL string
public
interpolate(string $expr, null|array<string|int, mixed> $args[, string $activeMode = self::INTERPOLATE_INPUT ]) : string
Parameters
- $expr : string
-
SQL expression
- $args : null|array<string|int, mixed>
-
a list of values to insert into the SQL expression; keys are prefix-coded: prefix '@' => escape SQL prefix '#' => literal number, skip escaping but do validation prefix '!' => literal, skip escaping and validation if a value is an array, then it will be imploded
PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
- $activeMode : string = self::INTERPOLATE_INPUT
Return values
stringisEmpty()
public
isEmpty([array<string|int, mixed>|null $parts = NULL ]) : bool
Parameters
- $parts : array<string|int, mixed>|null = NULL
-
List of fields to check (e.g. 'selects', 'joins'). Defaults to all.
Return values
booljoin()
Add a new JOIN clause.
public
join(string|null $name, string|array<string|int, mixed> $exprs[, array<string|int, mixed>|null $args = NULL ]) : CRM_Utils_SQL_Select
Note: To add multiple JOINs at once, use $name===NULL and pass an array of $exprs.
Parameters
- $name : string|null
-
The effective alias of the joined table.
- $exprs : string|array<string|int, mixed>
-
The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id").
- $args : array<string|int, mixed>|null = NULL
Return values
CRM_Utils_SQL_Selectlimit()
Set a limit on the number of records to return.
public
limit(int $limit[, int $offset = 0 ]) : CRM_Utils_SQL_Select
Parameters
- $limit : int
- $offset : int = 0
Tags
Return values
CRM_Utils_SQL_Selectmerge()
Merge something or other.
public
merge(array<string|int, mixed>|CRM_Utils_SQL_Select $other[, array<string|int, mixed>|null $parts = NULL ]) : CRM_Utils_SQL_Select
Parameters
- $other : array<string|int, mixed>|CRM_Utils_SQL_Select
- $parts : array<string|int, mixed>|null = NULL
-
ex: 'joins', 'wheres'
Return values
CRM_Utils_SQL_SelectoffsetExists()
Has an offset been set.
public
offsetExists(string $offset) : bool
Parameters
- $offset : string
Return values
booloffsetGet()
Get the value of a SQL parameter.
public
offsetGet(string $offset) : mixed
$select['cid'] = 123;
$select->where('contact.id = #cid');
echo $select['cid'];
Parameters
- $offset : string
Tags
Attributes
- #[ReturnTypeWillChange]
offsetSet()
Set the value of a SQL parameter.
public
offsetSet(string $offset, mixed $value) : void
$select['cid'] = 123;
$select->where('contact.id = #cid');
echo $select['cid'];
Parameters
- $offset : string
- $value : mixed
-
The new value of the parameter. Values may be strings, ints, or arrays thereof -- provided that the SQL query uses appropriate prefix (e.g. "@", "!", "#").
Tags
offsetUnset()
Unset the value of a SQL parameter.
public
offsetUnset(string $offset) : void
Parameters
- $offset : string
Tags
onDuplicate()
For INSERT INTO...SELECT...' queries, you may give an "ON DUPLICATE UPDATE" clause.
public
onDuplicate(string|array<string|int, mixed> $exprs[, null|array<string|int, mixed> $args = NULL ]) : CRM_Utils_SQL_Select
Parameters
- $exprs : string|array<string|int, mixed>
-
list of SQL expressions
- $args : null|array<string|int, mixed> = NULL
-
use NULL to disable interpolation; use an array of variables to enable
Return values
CRM_Utils_SQL_SelectorderBy()
Sort results by adding extra items to the ORDER BY clause.
public
orderBy(string|array<string|int, mixed> $exprs[, null|array<string|int, mixed> $args = NULL ][, int $weight = 0 ]) : CRM_Utils_SQL_Select
Parameters
- $exprs : string|array<string|int, mixed>
-
list of SQL expressions
- $args : null|array<string|int, mixed> = NULL
-
use NULL to disable interpolation; use an array of variables to enable
- $weight : int = 0
Return values
CRM_Utils_SQL_Selectparam()
Set one (or multiple) parameters to interpolate into the query.
public
param(array<string|int, mixed>|string $keys[, null|mixed $value = NULL ]) : CRM_Utils_SQL_Select
Parameters
- $keys : array<string|int, mixed>|string
-
Key name, or an array of key-value pairs.
- $value : null|mixed = NULL
-
The new value of the parameter. Values may be strings, ints, or arrays thereof -- provided that the SQL query uses appropriate prefix (e.g. "@", "!", "#").
Return values
CRM_Utils_SQL_SelectreplaceInto()
Wrapper function of insertInto fn but sets insertVerb = "REPLACE INTO "
public
replaceInto(string $table[, array<string|int, mixed> $fields = [] ]) : mixed
Parameters
- $table : string
-
The name of the other table (which receives new data).
- $fields : array<string|int, mixed> = []
-
The fields to fill in the other table (in order).
select()
Specify the column(s)/value(s) to return by adding to the SELECT clause
public
select(string|array<string|int, mixed> $exprs[, null|array<string|int, mixed> $args = NULL ]) : CRM_Utils_SQL_Select
Parameters
- $exprs : string|array<string|int, mixed>
-
list of SQL expressions
- $args : null|array<string|int, mixed> = NULL
-
use NULL to disable interpolation; use an array of variables to enable
Return values
CRM_Utils_SQL_SelectsetOp()
Add a set operation.
public
setOp(string $setOperation, array<string|int, CRM_Utils_SQL_Select>|CRM_Utils_SQL_Select $subQueries) : $this
Ex: CRM_Utils_SQL_Select::fromSet()->setOp('INTERSECT', [$subQuery1, $subQuery2])
Parameters
- $setOperation : string
-
Ex: 'UNION DISTINCT', 'UNION ALL'. TODO: 'INTERSECT', 'EXCEPT' when moving to MySQL 8.
- $subQueries : array<string|int, CRM_Utils_SQL_Select>|CRM_Utils_SQL_Select
Tags
Return values
$thisstrict()
Enable (or disable) strict mode.
public
strict([bool $strict = TRUE ]) : self
In strict mode, unknown variables will generate exceptions.
Parameters
- $strict : bool = TRUE
Return values
selfsyncInto()
Take the results of the SELECT query and copy them into another table.
public
syncInto(string $table, array<string|int, mixed>|string $keys, array<string|int, mixed> $mapping[, null|array<string|int, mixed> $args = NULL ]) : $this
If the same record already exists in the other table (based on primary-key or unique-key), then update the corresponding record.
Parameters
- $table : string
-
The table to write data into.
- $keys : array<string|int, mixed>|string
-
List of PK/unique fields NOTE: This must match the unique-key that was declared in the schema.
- $mapping : array<string|int, mixed>
-
List of values to select and where to send them.
For example, consider: ['relationship_id' => 'rel.id']
This would select the value of 'rel.id' and write to 'relationship_id'.
- $args : null|array<string|int, mixed> = NULL
-
Use NULL to skip interpolation; use an array of variables to enable.
Return values
$thistoSQL()
public
toSQL() : string
Return values
string —SQL statement
union()
Add a union to the list of set operations.
public
union(string $type, array<string|int, CRM_Utils_SQL_Select>|CRM_Utils_SQL_Select $subQueries) : $this
Ex: CRM_Utils_SQL_Select::fromSet()->union([$subQuery1, $subQuery2]) Ex: CRM_Utils_SQL_Select::fromSet()->union($subQuery1)->union($subQuery2);
Parameters
- $type : string
-
"DISTINCT"|"ALL"
- $subQueries : array<string|int, CRM_Utils_SQL_Select>|CRM_Utils_SQL_Select
Return values
$thiswhere()
Limit results by adding extra condition(s) to the WHERE clause
public
where(string|array<string|int, mixed> $exprs[, null|array<string|int, mixed> $args = NULL ]) : CRM_Utils_SQL_Select
Parameters
- $exprs : string|array<string|int, mixed>
-
list of SQL expressions
- $args : null|array<string|int, mixed> = NULL
-
use NULL to disable interpolation; use an array of variables to enable