3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
29 * Dear God Why Do I Have To Write This (Dumb SQL Builder)
33 * $select = CRM_Utils_SQL_Select::from('civicrm_activity act')
34 * ->join('absence', 'inner join civicrm_activity absence on absence.id = act.source_record_id')
35 * ->where('activity_type_id = #type', array('type' => 234))
36 * ->where('status_id IN (#statuses)', array('statuses' => array(1,2,3))
37 * ->where('subject like @subj', array('subj' => '%hello%'))
38 * ->where('!dynamicColumn = 1', array('dynamicColumn' => 'coalesce(is_active,0)'))
39 * ->where('!column = @value', array(
40 * 'column' => $customField->column_name,
41 * 'value' => $form['foo']
43 * echo $select->toSQL();
48 * - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
49 * - No knowledge of the underlying data model
50 * - SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
51 * - Variable escaping is concise and controllable based on prefixes, eg
52 * - similar to Drupal's t()
53 * - use "@varname" to insert the escaped value
54 * - use "!varname" to insert raw (unescaped) values
55 * - use "#varname" to insert a numerical value (these are validated but not escaped)
56 * - to disable any preprocessing, simply omit the variable list
57 * - control characters (@!#) are mandatory in expressions but optional in arg-keys
58 * - Variables may be individual values or arrays; arrays are imploded with commas
59 * - Conditionals are AND'd; if you need OR's, do it yourself
60 * - Use classes/functions with documentation (rather than undocumented array-trees)
61 * - For any given string, interpolation is only performed once. After an interpolation,
62 * a string may never again be subjected to interpolation.
64 * The "interpolate-once" principle can be enforced by either interpolating on input
65 * xor output. The notations for input and output interpolation are a bit different,
66 * and they may not be mixed.
69 * // Interpolate on input. Set params when using them.
70 * $select->where('activity_type_id = #type', array(
74 * // Interpolate on output. Set params independently.
76 * ->where('activity_type_id = #type')
77 * ->param('type', 234),
81 * @copyright CiviCRM LLC (c) 2004-2018
83 class CRM_Utils_SQL_Select
extends CRM_Utils_SQL_BaseParamQuery
{
85 private $insertInto = NULL;
86 private $insertVerb = 'INSERT INTO ';
87 private $insertIntoFields = array();
88 private $selects = array();
90 private $joins = array();
91 private $wheres = array();
92 private $groupBys = array();
93 private $havings = array();
94 private $orderBys = array();
95 private $limit = NULL;
96 private $offset = NULL;
97 private $distinct = NULL;
100 * Create a new SELECT query.
102 * @param string $from
103 * Table-name and optional alias.
104 * @param array $options
105 * @return CRM_Utils_SQL_Select
107 public static function from($from, $options = array()) {
108 return new self($from, $options);
112 * Create a partial SELECT query.
114 * @param array $options
115 * @return CRM_Utils_SQL_Select
117 public static function fragment($options = array()) {
118 return new self(NULL, $options);
122 * Create a new SELECT query.
124 * @param string $from
125 * Table-name and optional alias.
126 * @param array $options
128 public function __construct($from, $options = array()) {
130 $this->mode
= isset($options['mode']) ?
$options['mode'] : self
::INTERPOLATE_AUTO
;
134 * Make a new copy of this query.
136 * @return CRM_Utils_SQL_Select
138 public function copy() {
143 * Merge something or other.
145 * @param CRM_Utils_SQL_Select $other
146 * @param array|NULL $parts
147 * ex: 'joins', 'wheres'
148 * @return CRM_Utils_SQL_Select
150 public function merge($other, $parts = NULL) {
151 if ($other === NULL) {
155 if ($this->mode
=== self
::INTERPOLATE_AUTO
) {
156 $this->mode
= $other->mode
;
158 elseif ($other->mode
=== self
::INTERPOLATE_AUTO
) {
161 elseif ($this->mode
!== $other->mode
) {
162 // Mixing modes will lead to someone getting an expected substitution.
163 throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode}).");
166 $arrayFields = array('insertIntoFields', 'selects', 'joins', 'wheres', 'groupBys', 'havings', 'orderBys', 'params');
167 foreach ($arrayFields as $f) {
168 if ($parts === NULL ||
in_array($f, $parts)) {
169 $this->{$f} = array_merge($this->{$f}, $other->{$f});
173 $flatFields = array('insertInto', 'from', 'limit', 'offset');
174 foreach ($flatFields as $f) {
175 if ($parts === NULL ||
in_array($f, $parts)) {
176 if ($other->{$f} !== NULL) {
177 $this->{$f} = $other->{$f};
186 * Add a new JOIN clause.
188 * Note: To add multiple JOINs at once, use $name===NULL and
189 * pass an array of $exprs.
191 * @param string|NULL $name
192 * The effective alias of the joined table.
193 * @param string|array $exprs
194 * The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id").
195 * @param array|null $args
196 * @return CRM_Utils_SQL_Select
198 public function join($name, $exprs, $args = NULL) {
199 if ($name !== NULL) {
200 $this->joins
[$name] = $this->interpolate($exprs, $args);
203 foreach ($exprs as $name => $expr) {
204 $this->joins
[$name] = $this->interpolate($expr, $args);
212 * Specify the column(s)/value(s) to return by adding to the SELECT clause
214 * @param string|array $exprs list of SQL expressions
215 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
216 * @return CRM_Utils_SQL_Select
218 public function select($exprs, $args = NULL) {
219 $exprs = (array) $exprs;
220 foreach ($exprs as $expr) {
221 $this->selects
[] = $this->interpolate($expr, $args);
227 * Return only distinct values
229 * @param bool $isDistinct allow DISTINCT select or not
230 * @return CRM_Utils_SQL_Select
232 public function distinct($isDistinct = TRUE) {
234 $this->distinct
= 'DISTINCT ';
240 * Limit results by adding extra condition(s) to the WHERE clause
242 * @param string|array $exprs list of SQL expressions
243 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
244 * @return CRM_Utils_SQL_Select
246 public function where($exprs, $args = NULL) {
247 $exprs = (array) $exprs;
248 foreach ($exprs as $expr) {
249 $evaluatedExpr = $this->interpolate($expr, $args);
250 $this->wheres
[$evaluatedExpr] = $evaluatedExpr;
256 * Group results by adding extra items to the GROUP BY clause.
258 * @param string|array $exprs list of SQL expressions
259 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
260 * @return CRM_Utils_SQL_Select
262 public function groupBy($exprs, $args = NULL) {
263 $exprs = (array) $exprs;
264 foreach ($exprs as $expr) {
265 $evaluatedExpr = $this->interpolate($expr, $args);
266 $this->groupBys
[$evaluatedExpr] = $evaluatedExpr;
272 * Limit results by adding extra condition(s) to the HAVING clause
274 * @param string|array $exprs list of SQL expressions
275 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
276 * @return CRM_Utils_SQL_Select
278 public function having($exprs, $args = NULL) {
279 $exprs = (array) $exprs;
280 foreach ($exprs as $expr) {
281 $evaluatedExpr = $this->interpolate($expr, $args);
282 $this->havings
[$evaluatedExpr] = $evaluatedExpr;
288 * Sort results by adding extra items to the ORDER BY clause.
290 * @param string|array $exprs list of SQL expressions
291 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
293 * @return \CRM_Utils_SQL_Select
295 public function orderBy($exprs, $args = NULL, $weight = 0) {
297 $exprs = (array) $exprs;
298 foreach ($exprs as $expr) {
299 $evaluatedExpr = $this->interpolate($expr, $args);
300 $this->orderBys
[$evaluatedExpr] = array('value' => $evaluatedExpr, 'weight' => $weight, 'guid' => $guid++
);
306 * Set one (or multiple) parameters to interpolate into the query.
308 * @param array|string $keys
309 * Key name, or an array of key-value pairs.
310 * @param null|mixed $value
311 * The new value of the parameter.
312 * Values may be strings, ints, or arrays thereof -- provided that the
313 * SQL query uses appropriate prefix (e.g. "@", "!", "#").
314 * @return \CRM_Utils_SQL_Select
316 public function param($keys, $value = NULL) {
317 // Why bother with an override? To provide bett er type-hinting in `@return`.
318 return parent
::param($keys, $value);
322 * Set a limit on the number of records to return.
326 * @return CRM_Utils_SQL_Select
327 * @throws CRM_Core_Exception
329 public function limit($limit, $offset = 0) {
330 if ($limit !== NULL && !is_numeric($limit)) {
331 throw new CRM_Core_Exception("Illegal limit");
333 if ($offset !== NULL && !is_numeric($offset)) {
334 throw new CRM_Core_Exception("Illegal offset");
336 $this->limit
= $limit;
337 $this->offset
= $offset;
342 * Insert the results of the SELECT query into another
345 * @param string $table
346 * The name of the other table (which receives new data).
347 * @param array $fields
348 * The fields to fill in the other table (in order).
349 * @return CRM_Utils_SQL_Select
350 * @see insertIntoField
352 public function insertInto($table, $fields = array()) {
353 $this->insertInto
= $table;
354 $this->insertIntoField($fields);
359 * Wrapper function of insertInto fn but sets insertVerb = "INSERT IGNORE INTO "
361 * @param string $table
362 * The name of the other table (which receives new data).
363 * @param array $fields
364 * The fields to fill in the other table (in order).
365 * @return CRM_Utils_SQL_Select
367 public function insertIgnoreInto($table, $fields = array()) {
368 $this->insertVerb
= "INSERT IGNORE INTO ";
369 return $this->insertInto($table, $fields);
373 * Wrapper function of insertInto fn but sets insertVerb = "REPLACE INTO "
375 * @param string $table
376 * The name of the other table (which receives new data).
377 * @param array $fields
378 * The fields to fill in the other table (in order).
380 public function replaceInto($table, $fields = array()) {
381 $this->insertVerb
= "REPLACE INTO ";
382 return $this->insertInto($table, $fields);
387 * @param array $fields
388 * The fields to fill in the other table (in order).
389 * @return CRM_Utils_SQL_Select
391 public function insertIntoField($fields) {
392 $fields = (array) $fields;
393 foreach ($fields as $field) {
394 $this->insertIntoFields
[] = $field;
400 * @param array|NULL $parts
401 * List of fields to check (e.g. 'selects', 'joins').
405 public function isEmpty($parts = NULL) {
420 if ($parts !== NULL) {
421 $fields = array_intersect($fields, $parts);
423 foreach ($fields as $field) {
424 if (!empty($this->{$field})) {
435 public function toSQL() {
437 if ($this->insertInto
) {
438 $sql .= $this->insertVerb
. $this->insertInto
. ' (';
439 $sql .= implode(', ', $this->insertIntoFields
);
443 if ($this->selects
) {
444 $sql .= 'SELECT ' . $this->distinct
. implode(', ', $this->selects
) . "\n";
447 $sql .= 'SELECT *' . "\n";
449 if ($this->from
!== NULL) {
450 $sql .= 'FROM ' . $this->from
. "\n";
452 foreach ($this->joins
as $join) {
453 $sql .= $join . "\n";
456 $sql .= 'WHERE (' . implode(') AND (', $this->wheres
) . ")\n";
458 if ($this->groupBys
) {
459 $sql .= 'GROUP BY ' . implode(', ', $this->groupBys
) . "\n";
461 if ($this->havings
) {
462 $sql .= 'HAVING (' . implode(') AND (', $this->havings
) . ")\n";
464 if ($this->orderBys
) {
465 $orderBys = CRM_Utils_Array
::crmArraySortByField($this->orderBys
,
466 array('weight', 'guid'));
467 $orderBys = CRM_Utils_Array
::collect('value', $orderBys);
468 $sql .= 'ORDER BY ' . implode(', ', $orderBys) . "\n";
470 if ($this->limit
!== NULL) {
471 $sql .= 'LIMIT ' . $this->limit
. "\n";
472 if ($this->offset
!== NULL) {
473 $sql .= 'OFFSET ' . $this->offset
. "\n";
476 if ($this->mode
=== self
::INTERPOLATE_OUTPUT
) {
477 $sql = $this->interpolate($sql, $this->params
, self
::INTERPOLATE_OUTPUT
);
485 * To examine the results, use a function like `fetch()`, `fetchAll()`,
486 * `fetchValue()`, or `fetchMap()`.
488 * @param string|NULL $daoName
489 * The return object should be an instance of this class.
490 * Ex: 'CRM_Contact_BAO_Contact'.
491 * @param bool $i18nRewrite
492 * If the system has multilingual features, should the field/table
493 * names be rewritten?
494 * @return CRM_Core_DAO
495 * @see CRM_Core_DAO::executeQuery
496 * @see CRM_Core_I18n_Schema::rewriteQuery
498 public function execute($daoName = NULL, $i18nRewrite = TRUE) {
499 // Don't pass through $params. toSQL() handles interpolation.
502 // Don't pass through $abort, $trapException. Just use straight-up exceptions.
504 $trapException = FALSE;
505 $errorScope = CRM_Core_TemporaryErrorScope
::useException();
507 // Don't pass through freeDAO. You can do it yourself.
510 return CRM_Core_DAO
::executeQuery($this->toSQL(), $params, $abort, $daoName,
511 $freeDAO, $i18nRewrite, $trapException);