3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
13 * Dear God Why Do I Have To Write This (Dumb SQL Builder)
17 * $select = CRM_Utils_SQL_Select::from('civicrm_activity act')
18 * ->join('absence', 'inner join civicrm_activity absence on absence.id = act.source_record_id')
19 * ->where('activity_type_id = #type', array('type' => 234))
20 * ->where('status_id IN (#statuses)', array('statuses' => array(1,2,3))
21 * ->where('subject like @subj', array('subj' => '%hello%'))
22 * ->where('!dynamicColumn = 1', array('dynamicColumn' => 'coalesce(is_active,0)'))
23 * ->where('!column = @value', array(
24 * 'column' => $customField->column_name,
25 * 'value' => $form['foo']
27 * echo $select->toSQL();
32 * - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
33 * - No knowledge of the underlying data model
34 * - SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
35 * - Variable escaping is concise and controllable based on prefixes, eg
36 * - similar to Drupal's t()
37 * - use "@varname" to insert the escaped value
38 * - use "!varname" to insert raw (unescaped) values
39 * - use "#varname" to insert a numerical value (these are validated but not escaped)
40 * - to disable any preprocessing, simply omit the variable list
41 * - control characters (@!#) are mandatory in expressions but optional in arg-keys
42 * - Variables may be individual values or arrays; arrays are imploded with commas
43 * - Conditionals are AND'd; if you need OR's, do it yourself
44 * - Use classes/functions with documentation (rather than undocumented array-trees)
45 * - For any given string, interpolation is only performed once. After an interpolation,
46 * a string may never again be subjected to interpolation.
48 * The "interpolate-once" principle can be enforced by either interpolating on input
49 * xor output. The notations for input and output interpolation are a bit different,
50 * and they may not be mixed.
53 * // Interpolate on input. Set params when using them.
54 * $select->where('activity_type_id = #type', array(
58 * // Interpolate on output. Set params independently.
60 * ->where('activity_type_id = #type')
61 * ->param('type', 234),
65 * @copyright CiviCRM LLC https://civicrm.org/licensing
67 class CRM_Utils_SQL_Select
extends CRM_Utils_SQL_BaseParamQuery
{
69 private $insertInto = NULL;
70 private $insertVerb = 'INSERT INTO ';
71 private $insertIntoFields = [];
72 private $onDuplicates = [];
73 private $selects = [];
77 private $groupBys = [];
78 private $havings = [];
79 private $orderBys = [];
80 private $limit = NULL;
81 private $offset = NULL;
82 private $distinct = NULL;
85 * Create a new SELECT query.
88 * Table-name and optional alias.
89 * @param array $options
90 * @return CRM_Utils_SQL_Select
92 public static function from($from, $options = []) {
93 return new self($from, $options);
97 * Create a partial SELECT query.
99 * @param array $options
100 * @return CRM_Utils_SQL_Select
102 public static function fragment($options = []) {
103 return new self(NULL, $options);
107 * Create a new SELECT query.
109 * @param string $from
110 * Table-name and optional alias.
111 * @param array $options
113 public function __construct($from, $options = []) {
115 $this->mode
= $options['mode'] ?? self
::INTERPOLATE_AUTO
;
119 * Make a new copy of this query.
121 * @return CRM_Utils_SQL_Select
123 public function copy() {
128 * Merge something or other.
130 * @param array|CRM_Utils_SQL_Select $other
131 * @param array|NULL $parts
132 * ex: 'joins', 'wheres'
133 * @return CRM_Utils_SQL_Select
135 public function merge($other, $parts = NULL) {
136 if ($other === NULL) {
140 if (is_array($other)) {
141 foreach ($other as $fragment) {
142 $this->merge($fragment, $parts);
147 if ($this->mode
=== self
::INTERPOLATE_AUTO
) {
148 $this->mode
= $other->mode
;
150 elseif ($other->mode
=== self
::INTERPOLATE_AUTO
) {
153 elseif ($this->mode
!== $other->mode
) {
154 // Mixing modes will lead to someone getting an expected substitution.
155 throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode}).");
158 $arrayFields = ['insertIntoFields', 'selects', 'joins', 'wheres', 'groupBys', 'havings', 'orderBys', 'params'];
159 foreach ($arrayFields as $f) {
160 if ($parts === NULL ||
in_array($f, $parts)) {
161 $this->{$f} = array_merge($this->{$f}, $other->{$f});
165 $flatFields = ['insertInto', 'from', 'limit', 'offset'];
166 foreach ($flatFields as $f) {
167 if ($parts === NULL ||
in_array($f, $parts)) {
168 if ($other->{$f} !== NULL) {
169 $this->{$f} = $other->{$f};
178 * Add a new JOIN clause.
180 * Note: To add multiple JOINs at once, use $name===NULL and
181 * pass an array of $exprs.
183 * @param string|NULL $name
184 * The effective alias of the joined table.
185 * @param string|array $exprs
186 * The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id").
187 * @param array|null $args
188 * @return CRM_Utils_SQL_Select
190 public function join($name, $exprs, $args = NULL) {
191 if ($name !== NULL) {
192 $this->joins
[$name] = $this->interpolate($exprs, $args);
195 foreach ($exprs as $name => $expr) {
196 $this->joins
[$name] = $this->interpolate($expr, $args);
204 * Specify the column(s)/value(s) to return by adding to the SELECT clause
206 * @param string|array $exprs list of SQL expressions
207 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
208 * @return CRM_Utils_SQL_Select
210 public function select($exprs, $args = NULL) {
211 $exprs = (array) $exprs;
212 foreach ($exprs as $expr) {
213 $this->selects
[] = $this->interpolate($expr, $args);
219 * Return only distinct values
221 * @param bool $isDistinct allow DISTINCT select or not
222 * @return CRM_Utils_SQL_Select
224 public function distinct($isDistinct = TRUE) {
226 $this->distinct
= 'DISTINCT ';
232 * Limit results by adding extra condition(s) to the WHERE clause
234 * @param string|array $exprs list of SQL expressions
235 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
236 * @return CRM_Utils_SQL_Select
238 public function where($exprs, $args = NULL) {
239 $exprs = (array) $exprs;
240 foreach ($exprs as $expr) {
241 $evaluatedExpr = $this->interpolate($expr, $args);
242 $this->wheres
[$evaluatedExpr] = $evaluatedExpr;
248 * Group results by adding extra items to the GROUP BY clause.
250 * @param string|array $exprs list of SQL expressions
251 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
252 * @return CRM_Utils_SQL_Select
254 public function groupBy($exprs, $args = NULL) {
255 $exprs = (array) $exprs;
256 foreach ($exprs as $expr) {
257 $evaluatedExpr = $this->interpolate($expr, $args);
258 $this->groupBys
[$evaluatedExpr] = $evaluatedExpr;
264 * Limit results by adding extra condition(s) to the HAVING clause
266 * @param string|array $exprs list of SQL expressions
267 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
268 * @return CRM_Utils_SQL_Select
270 public function having($exprs, $args = NULL) {
271 $exprs = (array) $exprs;
272 foreach ($exprs as $expr) {
273 $evaluatedExpr = $this->interpolate($expr, $args);
274 $this->havings
[$evaluatedExpr] = $evaluatedExpr;
280 * Sort results by adding extra items to the ORDER BY clause.
282 * @param string|array $exprs list of SQL expressions
283 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
285 * @return \CRM_Utils_SQL_Select
287 public function orderBy($exprs, $args = NULL, $weight = 0) {
289 $exprs = (array) $exprs;
290 foreach ($exprs as $expr) {
291 $evaluatedExpr = $this->interpolate($expr, $args);
292 $this->orderBys
[$evaluatedExpr] = ['value' => $evaluatedExpr, 'weight' => $weight, 'guid' => $guid++
];
298 * Set one (or multiple) parameters to interpolate into the query.
300 * @param array|string $keys
301 * Key name, or an array of key-value pairs.
302 * @param null|mixed $value
303 * The new value of the parameter.
304 * Values may be strings, ints, or arrays thereof -- provided that the
305 * SQL query uses appropriate prefix (e.g. "@", "!", "#").
306 * @return \CRM_Utils_SQL_Select
308 public function param($keys, $value = NULL) {
309 // Why bother with an override? To provide bett er type-hinting in `@return`.
310 return parent
::param($keys, $value);
314 * Set a limit on the number of records to return.
318 * @return CRM_Utils_SQL_Select
319 * @throws CRM_Core_Exception
321 public function limit($limit, $offset = 0) {
322 if ($limit !== NULL && !is_numeric($limit)) {
323 throw new CRM_Core_Exception("Illegal limit");
325 if ($offset !== NULL && !is_numeric($offset)) {
326 throw new CRM_Core_Exception("Illegal offset");
328 $this->limit
= $limit;
329 $this->offset
= $offset;
334 * Insert the results of the SELECT query into another
337 * @param string $table
338 * The name of the other table (which receives new data).
339 * @param array $fields
340 * The fields to fill in the other table (in order).
341 * @return CRM_Utils_SQL_Select
342 * @see insertIntoField
344 public function insertInto($table, $fields = []) {
345 $this->insertInto
= $table;
346 $this->insertIntoField($fields);
351 * Wrapper function of insertInto fn but sets insertVerb = "INSERT IGNORE INTO "
353 * @param string $table
354 * The name of the other table (which receives new data).
355 * @param array $fields
356 * The fields to fill in the other table (in order).
357 * @return CRM_Utils_SQL_Select
359 public function insertIgnoreInto($table, $fields = []) {
360 $this->insertVerb
= "INSERT IGNORE INTO ";
361 return $this->insertInto($table, $fields);
365 * Wrapper function of insertInto fn but sets insertVerb = "REPLACE INTO "
367 * @param string $table
368 * The name of the other table (which receives new data).
369 * @param array $fields
370 * The fields to fill in the other table (in order).
372 public function replaceInto($table, $fields = []) {
373 $this->insertVerb
= "REPLACE INTO ";
374 return $this->insertInto($table, $fields);
378 * Take the results of the SELECT query and copy them into another
381 * If the same record already exists in the other table (based on
382 * primary-key or unique-key), then update the corresponding record.
384 * @param string $table
385 * The table to write data into.
386 * @param array|string $keys
387 * List of PK/unique fields
388 * NOTE: This must match the unique-key that was declared in the schema.
389 * @param array $mapping
390 * List of values to select and where to send them.
392 * For example, consider:
393 * ['relationship_id' => 'rel.id']
395 * This would select the value of 'rel.id' and write to 'relationship_id'.
397 * @param null|array $args
398 * Use NULL to skip interpolation; use an array of variables to enable.
401 public function syncInto($table, $keys, $mapping, $args = NULL) {
402 $keys = (array) $keys;
404 $this->select(array_values($mapping), $args);
405 $this->insertInto($table, array_keys($mapping));
407 foreach ($mapping as $intoColumn => $fromValue) {
408 if (!in_array($intoColumn, $keys)) {
409 $this->onDuplicate("$intoColumn = $fromValue", $args);
417 * @param array $fields
418 * The fields to fill in the other table (in order).
419 * @return CRM_Utils_SQL_Select
421 public function insertIntoField($fields) {
422 $fields = (array) $fields;
423 foreach ($fields as $field) {
424 $this->insertIntoFields
[] = $field;
430 * For INSERT INTO...SELECT...' queries, you may give an "ON DUPLICATE UPDATE" clause.
432 * @param string|array $exprs list of SQL expressions
433 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
434 * @return CRM_Utils_SQL_Select
436 public function onDuplicate($exprs, $args = NULL) {
437 $exprs = (array) $exprs;
438 foreach ($exprs as $expr) {
439 $evaluatedExpr = $this->interpolate($expr, $args);
440 $this->onDuplicates
[$evaluatedExpr] = $evaluatedExpr;
446 * @param array|NULL $parts
447 * List of fields to check (e.g. 'selects', 'joins').
451 public function isEmpty($parts = NULL) {
466 if ($parts !== NULL) {
467 $fields = array_intersect($fields, $parts);
469 foreach ($fields as $field) {
470 if (!empty($this->{$field})) {
481 public function toSQL() {
483 if ($this->insertInto
) {
484 $sql .= $this->insertVerb
. $this->insertInto
. ' (';
485 $sql .= implode(', ', $this->insertIntoFields
);
489 if ($this->selects
) {
490 $sql .= 'SELECT ' . $this->distinct
. implode(', ', $this->selects
) . "\n";
493 $sql .= 'SELECT *' . "\n";
495 if ($this->from
!== NULL) {
496 $sql .= 'FROM ' . $this->from
. "\n";
498 foreach ($this->joins
as $join) {
499 $sql .= $join . "\n";
502 $sql .= 'WHERE (' . implode(') AND (', $this->wheres
) . ")\n";
504 if ($this->groupBys
) {
505 $sql .= 'GROUP BY ' . implode(', ', $this->groupBys
) . "\n";
507 if ($this->havings
) {
508 $sql .= 'HAVING (' . implode(') AND (', $this->havings
) . ")\n";
510 if ($this->orderBys
) {
511 $orderBys = CRM_Utils_Array
::crmArraySortByField($this->orderBys
,
513 $orderBys = CRM_Utils_Array
::collect('value', $orderBys);
514 $sql .= 'ORDER BY ' . implode(', ', $orderBys) . "\n";
516 if ($this->limit
!== NULL) {
517 $sql .= 'LIMIT ' . $this->limit
. "\n";
518 if ($this->offset
!== NULL) {
519 $sql .= 'OFFSET ' . $this->offset
. "\n";
522 if ($this->onDuplicates
) {
523 if ($this->insertVerb
=== 'INSERT INTO ') {
524 $sql .= ' ON DUPLICATE KEY UPDATE ' . implode(", ", $this->onDuplicates
) . "\n";
527 throw new \
Exception("The ON DUPLICATE clause and only be used with INSERT INTO queries.");
531 if ($this->mode
=== self
::INTERPOLATE_OUTPUT
) {
532 $sql = $this->interpolate($sql, $this->params
, self
::INTERPOLATE_OUTPUT
);
540 * To examine the results, use a function like `fetch()`, `fetchAll()`,
541 * `fetchValue()`, or `fetchMap()`.
543 * @param string|NULL $daoName
544 * The return object should be an instance of this class.
545 * Ex: 'CRM_Contact_BAO_Contact'.
546 * @param bool $i18nRewrite
547 * If the system has multilingual features, should the field/table
548 * names be rewritten?
549 * @return CRM_Core_DAO
550 * @see CRM_Core_DAO::executeQuery
551 * @see CRM_Core_I18n_Schema::rewriteQuery
553 public function execute($daoName = NULL, $i18nRewrite = TRUE) {
554 // Don't pass through $params. toSQL() handles interpolation.
557 // Don't pass through $abort, $trapException. Just use straight-up exceptions.
559 $trapException = FALSE;
561 // Don't pass through freeDAO. You can do it yourself.
564 return CRM_Core_DAO
::executeQuery($this->toSQL(), $params, $abort, $daoName,
565 $freeDAO, $i18nRewrite, $trapException);