CRM/Utils/SQL/Select.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   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  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  * Dear God Why Do I Have To Write This (Dumb SQL Builder)
  14  *
  15  * Usage:
  16  * @code
  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']
  26  *      ))
  27  * echo $select->toSQL();
  28  * @endcode
  29  *
  30  * Design principles:
  31  *  - Portable
  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.
  47  *
  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.
  51  *
  52  * @code
  53  * // Interpolate on input. Set params when using them.
  54  * $select->where('activity_type_id = #type', array(
  55  *   'type' => 234,
  56  * ));
  57  *
  58  * // Interpolate on output. Set params independently.
  59  * $select
  60  *     ->where('activity_type_id = #type')
  61  *     ->param('type', 234),
  62  * @endcode
  63  *
  64  * @package CRM
  65  * @copyright CiviCRM LLC https://civicrm.org/licensing
  66  */
  67 class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery {
  68
  69   private $insertInto = NULL;
  70   private $insertVerb = 'INSERT INTO ';
  71   private $insertIntoFields = [];
  72   private $selects = [];
  73   private $from;
  74   private $joins = [];
  75   private $wheres = [];
  76   private $groupBys = [];
  77   private $havings = [];
  78   private $orderBys = [];
  79   private $limit = NULL;
  80   private $offset = NULL;
  81   private $distinct = NULL;
  82
  83   /**
  84    * Create a new SELECT query.
  85    *
  86    * @param string $from
  87    *   Table-name and optional alias.
  88    * @param array $options
  89    * @return CRM_Utils_SQL_Select
  90    */
  91   public static function from($from, $options = []) {
  92     return new self($from, $options);
  93   }
  94
  95   /**
  96    * Create a partial SELECT query.
  97    *
  98    * @param array $options
  99    * @return CRM_Utils_SQL_Select
 100    */
 101   public static function fragment($options = []) {
 102     return new self(NULL, $options);
 103   }
 104
 105   /**
 106    * Create a new SELECT query.
 107    *
 108    * @param string $from
 109    *   Table-name and optional alias.
 110    * @param array $options
 111    */
 112   public function __construct($from, $options = []) {
 113     $this->from = $from;
 114     $this->mode = isset($options['mode']) ? $options['mode'] : self::INTERPOLATE_AUTO;
 115   }
 116
 117   /**
 118    * Make a new copy of this query.
 119    *
 120    * @return CRM_Utils_SQL_Select
 121    */
 122   public function copy() {
 123     return clone $this;
 124   }
 125
 126   /**
 127    * Merge something or other.
 128    *
 129    * @param array|CRM_Utils_SQL_Select $other
 130    * @param array|NULL $parts
 131    *   ex: 'joins', 'wheres'
 132    * @return CRM_Utils_SQL_Select
 133    */
 134   public function merge($other, $parts = NULL) {
 135     if ($other === NULL) {
 136       return $this;
 137     }
 138
 139     if (is_array($other)) {
 140       foreach ($other as $fragment) {
 141         $this->merge($fragment, $parts);
 142       }
 143       return $this;
 144     }
 145
 146     if ($this->mode === self::INTERPOLATE_AUTO) {
 147       $this->mode = $other->mode;
 148     }
 149     elseif ($other->mode === self::INTERPOLATE_AUTO) {
 150       // Noop.
 151     }
 152     elseif ($this->mode !== $other->mode) {
 153       // Mixing modes will lead to someone getting an expected substitution.
 154       throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode}).");
 155     }
 156
 157     $arrayFields = ['insertIntoFields', 'selects', 'joins', 'wheres', 'groupBys', 'havings', 'orderBys', 'params'];
 158     foreach ($arrayFields as $f) {
 159       if ($parts === NULL || in_array($f, $parts)) {
 160         $this->{$f} = array_merge($this->{$f}, $other->{$f});
 161       }
 162     }
 163
 164     $flatFields = ['insertInto', 'from', 'limit', 'offset'];
 165     foreach ($flatFields as $f) {
 166       if ($parts === NULL || in_array($f, $parts)) {
 167         if ($other->{$f} !== NULL) {
 168           $this->{$f} = $other->{$f};
 169         }
 170       }
 171     }
 172
 173     return $this;
 174   }
 175
 176   /**
 177    * Add a new JOIN clause.
 178    *
 179    * Note: To add multiple JOINs at once, use $name===NULL and
 180    * pass an array of $exprs.
 181    *
 182    * @param string|NULL $name
 183    *   The effective alias of the joined table.
 184    * @param string|array $exprs
 185    *   The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id").
 186    * @param array|null $args
 187    * @return CRM_Utils_SQL_Select
 188    */
 189   public function join($name, $exprs, $args = NULL) {
 190     if ($name !== NULL) {
 191       $this->joins[$name] = $this->interpolate($exprs, $args);
 192     }
 193     else {
 194       foreach ($exprs as $name => $expr) {
 195         $this->joins[$name] = $this->interpolate($expr, $args);
 196       }
 197       return $this;
 198     }
 199     return $this;
 200   }
 201
 202   /**
 203    * Specify the column(s)/value(s) to return by adding to the SELECT clause
 204    *
 205    * @param string|array $exprs list of SQL expressions
 206    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 207    * @return CRM_Utils_SQL_Select
 208    */
 209   public function select($exprs, $args = NULL) {
 210     $exprs = (array) $exprs;
 211     foreach ($exprs as $expr) {
 212       $this->selects[] = $this->interpolate($expr, $args);
 213     }
 214     return $this;
 215   }
 216
 217   /**
 218    * Return only distinct values
 219    *
 220    * @param bool $isDistinct allow DISTINCT select or not
 221    * @return CRM_Utils_SQL_Select
 222    */
 223   public function distinct($isDistinct = TRUE) {
 224     if ($isDistinct) {
 225       $this->distinct = 'DISTINCT ';
 226     }
 227     return $this;
 228   }
 229
 230   /**
 231    * Limit results by adding extra condition(s) to the WHERE clause
 232    *
 233    * @param string|array $exprs list of SQL expressions
 234    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 235    * @return CRM_Utils_SQL_Select
 236    */
 237   public function where($exprs, $args = NULL) {
 238     $exprs = (array) $exprs;
 239     foreach ($exprs as $expr) {
 240       $evaluatedExpr = $this->interpolate($expr, $args);
 241       $this->wheres[$evaluatedExpr] = $evaluatedExpr;
 242     }
 243     return $this;
 244   }
 245
 246   /**
 247    * Group results by adding extra items to the GROUP BY clause.
 248    *
 249    * @param string|array $exprs list of SQL expressions
 250    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 251    * @return CRM_Utils_SQL_Select
 252    */
 253   public function groupBy($exprs, $args = NULL) {
 254     $exprs = (array) $exprs;
 255     foreach ($exprs as $expr) {
 256       $evaluatedExpr = $this->interpolate($expr, $args);
 257       $this->groupBys[$evaluatedExpr] = $evaluatedExpr;
 258     }
 259     return $this;
 260   }
 261
 262   /**
 263    * Limit results by adding extra condition(s) to the HAVING clause
 264    *
 265    * @param string|array $exprs list of SQL expressions
 266    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 267    * @return CRM_Utils_SQL_Select
 268    */
 269   public function having($exprs, $args = NULL) {
 270     $exprs = (array) $exprs;
 271     foreach ($exprs as $expr) {
 272       $evaluatedExpr = $this->interpolate($expr, $args);
 273       $this->havings[$evaluatedExpr] = $evaluatedExpr;
 274     }
 275     return $this;
 276   }
 277
 278   /**
 279    * Sort results by adding extra items to the ORDER BY clause.
 280    *
 281    * @param string|array $exprs list of SQL expressions
 282    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 283    * @param int $weight
 284    * @return \CRM_Utils_SQL_Select
 285    */
 286   public function orderBy($exprs, $args = NULL, $weight = 0) {
 287     static $guid = 0;
 288     $exprs = (array) $exprs;
 289     foreach ($exprs as $expr) {
 290       $evaluatedExpr = $this->interpolate($expr, $args);
 291       $this->orderBys[$evaluatedExpr] = ['value' => $evaluatedExpr, 'weight' => $weight, 'guid' => $guid++];
 292     }
 293     return $this;
 294   }
 295
 296   /**
 297    * Set one (or multiple) parameters to interpolate into the query.
 298    *
 299    * @param array|string $keys
 300    *   Key name, or an array of key-value pairs.
 301    * @param null|mixed $value
 302    *   The new value of the parameter.
 303    *   Values may be strings, ints, or arrays thereof -- provided that the
 304    *   SQL query uses appropriate prefix (e.g. "@", "!", "#").
 305    * @return \CRM_Utils_SQL_Select
 306    */
 307   public function param($keys, $value = NULL) {
 308     // Why bother with an override? To provide bett er type-hinting in `@return`.
 309     return parent::param($keys, $value);
 310   }
 311
 312   /**
 313    * Set a limit on the number of records to return.
 314    *
 315    * @param int $limit
 316    * @param int $offset
 317    * @return CRM_Utils_SQL_Select
 318    * @throws CRM_Core_Exception
 319    */
 320   public function limit($limit, $offset = 0) {
 321     if ($limit !== NULL && !is_numeric($limit)) {
 322       throw new CRM_Core_Exception("Illegal limit");
 323     }
 324     if ($offset !== NULL && !is_numeric($offset)) {
 325       throw new CRM_Core_Exception("Illegal offset");
 326     }
 327     $this->limit = $limit;
 328     $this->offset = $offset;
 329     return $this;
 330   }
 331
 332   /**
 333    * Insert the results of the SELECT query into another
 334    * table.
 335    *
 336    * @param string $table
 337    *   The name of the other table (which receives new data).
 338    * @param array $fields
 339    *   The fields to fill in the other table (in order).
 340    * @return CRM_Utils_SQL_Select
 341    * @see insertIntoField
 342    */
 343   public function insertInto($table, $fields = []) {
 344     $this->insertInto = $table;
 345     $this->insertIntoField($fields);
 346     return $this;
 347   }
 348
 349   /**
 350    * Wrapper function of insertInto fn but sets insertVerb = "INSERT IGNORE INTO "
 351    *
 352    * @param string $table
 353    *   The name of the other table (which receives new data).
 354    * @param array $fields
 355    *   The fields to fill in the other table (in order).
 356    * @return CRM_Utils_SQL_Select
 357    */
 358   public function insertIgnoreInto($table, $fields = []) {
 359     $this->insertVerb = "INSERT IGNORE INTO ";
 360     return $this->insertInto($table, $fields);
 361   }
 362
 363   /**
 364    * Wrapper function of insertInto fn but sets insertVerb = "REPLACE INTO "
 365    *
 366    * @param string $table
 367    *   The name of the other table (which receives new data).
 368    * @param array $fields
 369    *   The fields to fill in the other table (in order).
 370    */
 371   public function replaceInto($table, $fields = []) {
 372     $this->insertVerb = "REPLACE INTO ";
 373     return $this->insertInto($table, $fields);
 374   }
 375
 376   /**
 377    * @param array $fields
 378    *   The fields to fill in the other table (in order).
 379    * @return CRM_Utils_SQL_Select
 380    */
 381   public function insertIntoField($fields) {
 382     $fields = (array) $fields;
 383     foreach ($fields as $field) {
 384       $this->insertIntoFields[] = $field;
 385     }
 386     return $this;
 387   }
 388
 389   /**
 390    * @param array|NULL $parts
 391    *   List of fields to check (e.g. 'selects', 'joins').
 392    *   Defaults to all.
 393    * @return bool
 394    */
 395   public function isEmpty($parts = NULL) {
 396     $empty = TRUE;
 397     $fields = [
 398       'insertInto',
 399       'insertIntoFields',
 400       'selects',
 401       'from',
 402       'joins',
 403       'wheres',
 404       'groupBys',
 405       'havings',
 406       'orderBys',
 407       'limit',
 408       'offset',
 409     ];
 410     if ($parts !== NULL) {
 411       $fields = array_intersect($fields, $parts);
 412     }
 413     foreach ($fields as $field) {
 414       if (!empty($this->{$field})) {
 415         $empty = FALSE;
 416       }
 417     }
 418     return $empty;
 419   }
 420
 421   /**
 422    * @return string
 423    *   SQL statement
 424    */
 425   public function toSQL() {
 426     $sql = '';
 427     if ($this->insertInto) {
 428       $sql .= $this->insertVerb . $this->insertInto . ' (';
 429       $sql .= implode(', ', $this->insertIntoFields);
 430       $sql .= ")\n";
 431     }
 432
 433     if ($this->selects) {
 434       $sql .= 'SELECT ' . $this->distinct . implode(', ', $this->selects) . "\n";
 435     }
 436     else {
 437       $sql .= 'SELECT *' . "\n";
 438     }
 439     if ($this->from !== NULL) {
 440       $sql .= 'FROM ' . $this->from . "\n";
 441     }
 442     foreach ($this->joins as $join) {
 443       $sql .= $join . "\n";
 444     }
 445     if ($this->wheres) {
 446       $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n";
 447     }
 448     if ($this->groupBys) {
 449       $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n";
 450     }
 451     if ($this->havings) {
 452       $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n";
 453     }
 454     if ($this->orderBys) {
 455       $orderBys = CRM_Utils_Array::crmArraySortByField($this->orderBys,
 456         ['weight', 'guid']);
 457       $orderBys = CRM_Utils_Array::collect('value', $orderBys);
 458       $sql .= 'ORDER BY ' . implode(', ', $orderBys) . "\n";
 459     }
 460     if ($this->limit !== NULL) {
 461       $sql .= 'LIMIT ' . $this->limit . "\n";
 462       if ($this->offset !== NULL) {
 463         $sql .= 'OFFSET ' . $this->offset . "\n";
 464       }
 465     }
 466     if ($this->mode === self::INTERPOLATE_OUTPUT) {
 467       $sql = $this->interpolate($sql, $this->params, self::INTERPOLATE_OUTPUT);
 468     }
 469     return $sql;
 470   }
 471
 472   /**
 473    * Execute the query.
 474    *
 475    * To examine the results, use a function like `fetch()`, `fetchAll()`,
 476    * `fetchValue()`, or `fetchMap()`.
 477    *
 478    * @param string|NULL $daoName
 479    *   The return object should be an instance of this class.
 480    *   Ex: 'CRM_Contact_BAO_Contact'.
 481    * @param bool $i18nRewrite
 482    *   If the system has multilingual features, should the field/table
 483    *   names be rewritten?
 484    * @return CRM_Core_DAO
 485    * @see CRM_Core_DAO::executeQuery
 486    * @see CRM_Core_I18n_Schema::rewriteQuery
 487    */
 488   public function execute($daoName = NULL, $i18nRewrite = TRUE) {
 489     // Don't pass through $params. toSQL() handles interpolation.
 490     $params = [];
 491
 492     // Don't pass through $abort, $trapException. Just use straight-up exceptions.
 493     $abort = TRUE;
 494     $trapException = FALSE;
 495     $errorScope = CRM_Core_TemporaryErrorScope::useException();
 496
 497     // Don't pass through freeDAO. You can do it yourself.
 498     $freeDAO = FALSE;
 499
 500     return CRM_Core_DAO::executeQuery($this->toSQL(), $params, $abort, $daoName,
 501       $freeDAO, $i18nRewrite, $trapException);
 502   }
 503
 504 }