Commit | Line | Data |
---|---|---|
e2b5e5b1 | 1 | <?php |
d1d3c04a CW |
2 | /* |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
d1d3c04a | 5 | | | |
bc77d7c0 TO |
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 | | |
d1d3c04a CW |
9 | +--------------------------------------------------------------------+ |
10 | */ | |
e2b5e5b1 TO |
11 | |
12 | /** | |
13 | * Dear God Why Do I Have To Write This (Dumb SQL Builder) | |
14 | * | |
15 | * Usage: | |
0b882a86 | 16 | * ``` |
c5458931 | 17 | * $select = CRM_Utils_SQL_Select::from('civicrm_activity act') |
e2b5e5b1 | 18 | * ->join('absence', 'inner join civicrm_activity absence on absence.id = act.source_record_id') |
c5458931 TO |
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)')) | |
e2b5e5b1 | 23 | * ->where('!column = @value', array( |
c5458931 TO |
24 | * 'column' => $customField->column_name, |
25 | * 'value' => $form['foo'] | |
e2b5e5b1 TO |
26 | * )) |
27 | * echo $select->toSQL(); | |
0b882a86 | 28 | * ``` |
e2b5e5b1 TO |
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 | |
e2b5e5b1 TO |
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 | |
c5458931 | 41 | * - control characters (@!#) are mandatory in expressions but optional in arg-keys |
e2b5e5b1 TO |
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 | |
c3a8e3e5 | 44 | * - Use classes/functions with documentation (rather than undocumented array-trees) |
c5458931 TO |
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 | * | |
0b882a86 | 52 | * ``` |
c5458931 TO |
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), | |
0b882a86 | 62 | * ``` |
d1d3c04a CW |
63 | * |
64 | * @package CRM | |
ca5cec67 | 65 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
e2b5e5b1 | 66 | */ |
bfd417c7 | 67 | class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery { |
c5458931 | 68 | |
c5458931 | 69 | private $insertInto = NULL; |
6dd717a6 | 70 | private $insertVerb = 'INSERT INTO '; |
be2fb01f | 71 | private $insertIntoFields = []; |
fbf5eca6 | 72 | private $onDuplicates = []; |
be2fb01f | 73 | private $selects = []; |
e2b5e5b1 | 74 | private $from; |
be2fb01f CW |
75 | private $joins = []; |
76 | private $wheres = []; | |
77 | private $groupBys = []; | |
78 | private $havings = []; | |
79 | private $orderBys = []; | |
c3a8e3e5 TO |
80 | private $limit = NULL; |
81 | private $offset = NULL; | |
f8da3b93 | 82 | private $distinct = NULL; |
c5458931 | 83 | |
e2b5e5b1 | 84 | /** |
fe482240 | 85 | * Create a new SELECT query. |
e2b5e5b1 | 86 | * |
77855840 TO |
87 | * @param string $from |
88 | * Table-name and optional alias. | |
c5458931 TO |
89 | * @param array $options |
90 | * @return CRM_Utils_SQL_Select | |
91 | */ | |
be2fb01f | 92 | public static function from($from, $options = []) { |
c5458931 TO |
93 | return new self($from, $options); |
94 | } | |
95 | ||
96 | /** | |
97 | * Create a partial SELECT query. | |
98 | * | |
99 | * @param array $options | |
e2b5e5b1 TO |
100 | * @return CRM_Utils_SQL_Select |
101 | */ | |
be2fb01f | 102 | public static function fragment($options = []) { |
c5458931 | 103 | return new self(NULL, $options); |
e2b5e5b1 TO |
104 | } |
105 | ||
c3a8e3e5 | 106 | /** |
fe482240 | 107 | * Create a new SELECT query. |
c3a8e3e5 | 108 | * |
77855840 TO |
109 | * @param string $from |
110 | * Table-name and optional alias. | |
c5458931 | 111 | * @param array $options |
c3a8e3e5 | 112 | */ |
be2fb01f | 113 | public function __construct($from, $options = []) { |
e2b5e5b1 | 114 | $this->from = $from; |
2e1f50d6 | 115 | $this->mode = $options['mode'] ?? self::INTERPOLATE_AUTO; |
c5458931 TO |
116 | } |
117 | ||
118 | /** | |
119 | * Make a new copy of this query. | |
120 | * | |
121 | * @return CRM_Utils_SQL_Select | |
122 | */ | |
123 | public function copy() { | |
124 | return clone $this; | |
125 | } | |
126 | ||
127 | /** | |
3d469574 | 128 | * Merge something or other. |
129 | * | |
ec2eca5a | 130 | * @param array|CRM_Utils_SQL_Select $other |
2024d5b9 | 131 | * @param array|null $parts |
c5458931 | 132 | * ex: 'joins', 'wheres' |
14069c56 | 133 | * @return CRM_Utils_SQL_Select |
c5458931 TO |
134 | */ |
135 | public function merge($other, $parts = NULL) { | |
136 | if ($other === NULL) { | |
137 | return $this; | |
138 | } | |
139 | ||
ec2eca5a TO |
140 | if (is_array($other)) { |
141 | foreach ($other as $fragment) { | |
142 | $this->merge($fragment, $parts); | |
143 | } | |
144 | return $this; | |
145 | } | |
146 | ||
c5458931 TO |
147 | if ($this->mode === self::INTERPOLATE_AUTO) { |
148 | $this->mode = $other->mode; | |
149 | } | |
150 | elseif ($other->mode === self::INTERPOLATE_AUTO) { | |
151 | // Noop. | |
152 | } | |
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})."); | |
156 | } | |
157 | ||
be2fb01f | 158 | $arrayFields = ['insertIntoFields', 'selects', 'joins', 'wheres', 'groupBys', 'havings', 'orderBys', 'params']; |
c5458931 TO |
159 | foreach ($arrayFields as $f) { |
160 | if ($parts === NULL || in_array($f, $parts)) { | |
161 | $this->{$f} = array_merge($this->{$f}, $other->{$f}); | |
162 | } | |
163 | } | |
164 | ||
be2fb01f | 165 | $flatFields = ['insertInto', 'from', 'limit', 'offset']; |
c5458931 TO |
166 | foreach ($flatFields as $f) { |
167 | if ($parts === NULL || in_array($f, $parts)) { | |
168 | if ($other->{$f} !== NULL) { | |
169 | $this->{$f} = $other->{$f}; | |
170 | } | |
171 | } | |
172 | } | |
173 | ||
174 | return $this; | |
e2b5e5b1 TO |
175 | } |
176 | ||
c3a8e3e5 | 177 | /** |
fe482240 | 178 | * Add a new JOIN clause. |
c3a8e3e5 | 179 | * |
c5458931 TO |
180 | * Note: To add multiple JOINs at once, use $name===NULL and |
181 | * pass an array of $exprs. | |
182 | * | |
2024d5b9 | 183 | * @param string|null $name |
77855840 | 184 | * The effective alias of the joined table. |
c5458931 | 185 | * @param string|array $exprs |
77855840 | 186 | * The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id"). |
c3a8e3e5 TO |
187 | * @param array|null $args |
188 | * @return CRM_Utils_SQL_Select | |
189 | */ | |
c5458931 TO |
190 | public function join($name, $exprs, $args = NULL) { |
191 | if ($name !== NULL) { | |
192 | $this->joins[$name] = $this->interpolate($exprs, $args); | |
193 | } | |
194 | else { | |
195 | foreach ($exprs as $name => $expr) { | |
196 | $this->joins[$name] = $this->interpolate($expr, $args); | |
197 | } | |
198 | return $this; | |
199 | } | |
e2b5e5b1 TO |
200 | return $this; |
201 | } | |
202 | ||
203 | /** | |
c3a8e3e5 TO |
204 | * Specify the column(s)/value(s) to return by adding to the SELECT clause |
205 | * | |
e2b5e5b1 TO |
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 | |
209 | */ | |
210 | public function select($exprs, $args = NULL) { | |
211 | $exprs = (array) $exprs; | |
212 | foreach ($exprs as $expr) { | |
6e6685dc | 213 | $this->selects[] = $this->interpolate($expr, $args); |
e2b5e5b1 TO |
214 | } |
215 | return $this; | |
216 | } | |
217 | ||
f8da3b93 | 218 | /** |
219 | * Return only distinct values | |
220 | * | |
221 | * @param bool $isDistinct allow DISTINCT select or not | |
222 | * @return CRM_Utils_SQL_Select | |
223 | */ | |
224 | public function distinct($isDistinct = TRUE) { | |
225 | if ($isDistinct) { | |
226 | $this->distinct = 'DISTINCT '; | |
227 | } | |
228 | return $this; | |
229 | } | |
230 | ||
e2b5e5b1 | 231 | /** |
c3a8e3e5 TO |
232 | * Limit results by adding extra condition(s) to the WHERE clause |
233 | * | |
e2b5e5b1 TO |
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 | |
237 | */ | |
238 | public function where($exprs, $args = NULL) { | |
239 | $exprs = (array) $exprs; | |
240 | foreach ($exprs as $expr) { | |
fa7c2979 TO |
241 | $evaluatedExpr = $this->interpolate($expr, $args); |
242 | $this->wheres[$evaluatedExpr] = $evaluatedExpr; | |
e2b5e5b1 TO |
243 | } |
244 | return $this; | |
245 | } | |
246 | ||
247 | /** | |
fe482240 | 248 | * Group results by adding extra items to the GROUP BY clause. |
c3a8e3e5 | 249 | * |
e2b5e5b1 TO |
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 | |
253 | */ | |
254 | public function groupBy($exprs, $args = NULL) { | |
255 | $exprs = (array) $exprs; | |
256 | foreach ($exprs as $expr) { | |
6e6685dc C |
257 | $evaluatedExpr = $this->interpolate($expr, $args); |
258 | $this->groupBys[$evaluatedExpr] = $evaluatedExpr; | |
e2b5e5b1 TO |
259 | } |
260 | return $this; | |
261 | } | |
262 | ||
263 | /** | |
c3a8e3e5 TO |
264 | * Limit results by adding extra condition(s) to the HAVING clause |
265 | * | |
e2b5e5b1 TO |
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 | |
269 | */ | |
270 | public function having($exprs, $args = NULL) { | |
271 | $exprs = (array) $exprs; | |
272 | foreach ($exprs as $expr) { | |
6e6685dc C |
273 | $evaluatedExpr = $this->interpolate($expr, $args); |
274 | $this->havings[$evaluatedExpr] = $evaluatedExpr; | |
e2b5e5b1 TO |
275 | } |
276 | return $this; | |
277 | } | |
278 | ||
279 | /** | |
fe482240 | 280 | * Sort results by adding extra items to the ORDER BY clause. |
c3a8e3e5 | 281 | * |
e2b5e5b1 TO |
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 | |
4c6cc364 CW |
284 | * @param int $weight |
285 | * @return \CRM_Utils_SQL_Select | |
e2b5e5b1 | 286 | */ |
6db70618 TO |
287 | public function orderBy($exprs, $args = NULL, $weight = 0) { |
288 | static $guid = 0; | |
e2b5e5b1 TO |
289 | $exprs = (array) $exprs; |
290 | foreach ($exprs as $expr) { | |
6e6685dc | 291 | $evaluatedExpr = $this->interpolate($expr, $args); |
be2fb01f | 292 | $this->orderBys[$evaluatedExpr] = ['value' => $evaluatedExpr, 'weight' => $weight, 'guid' => $guid++]; |
e2b5e5b1 TO |
293 | } |
294 | return $this; | |
295 | } | |
296 | ||
c5458931 TO |
297 | /** |
298 | * Set one (or multiple) parameters to interpolate into the query. | |
299 | * | |
300 | * @param array|string $keys | |
301 | * Key name, or an array of key-value pairs. | |
302 | * @param null|mixed $value | |
1f7d270d TO |
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. "@", "!", "#"). | |
14069c56 | 306 | * @return \CRM_Utils_SQL_Select |
c5458931 TO |
307 | */ |
308 | public function param($keys, $value = NULL) { | |
bfd417c7 TO |
309 | // Why bother with an override? To provide bett er type-hinting in `@return`. |
310 | return parent::param($keys, $value); | |
c5458931 TO |
311 | } |
312 | ||
c3a8e3e5 | 313 | /** |
fe482240 | 314 | * Set a limit on the number of records to return. |
c3a8e3e5 TO |
315 | * |
316 | * @param int $limit | |
317 | * @param int $offset | |
318 | * @return CRM_Utils_SQL_Select | |
319 | * @throws CRM_Core_Exception | |
320 | */ | |
321 | public function limit($limit, $offset = 0) { | |
322 | if ($limit !== NULL && !is_numeric($limit)) { | |
323 | throw new CRM_Core_Exception("Illegal limit"); | |
324 | } | |
325 | if ($offset !== NULL && !is_numeric($offset)) { | |
326 | throw new CRM_Core_Exception("Illegal offset"); | |
327 | } | |
328 | $this->limit = $limit; | |
329 | $this->offset = $offset; | |
330 | return $this; | |
331 | } | |
332 | ||
c5458931 TO |
333 | /** |
334 | * Insert the results of the SELECT query into another | |
335 | * table. | |
336 | * | |
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). | |
14069c56 | 341 | * @return CRM_Utils_SQL_Select |
c5458931 TO |
342 | * @see insertIntoField |
343 | */ | |
be2fb01f | 344 | public function insertInto($table, $fields = []) { |
c5458931 TO |
345 | $this->insertInto = $table; |
346 | $this->insertIntoField($fields); | |
347 | return $this; | |
348 | } | |
349 | ||
6dd717a6 | 350 | /** |
351 | * Wrapper function of insertInto fn but sets insertVerb = "INSERT IGNORE INTO " | |
352 | * | |
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 | |
358 | */ | |
be2fb01f | 359 | public function insertIgnoreInto($table, $fields = []) { |
6dd717a6 | 360 | $this->insertVerb = "INSERT IGNORE INTO "; |
361 | return $this->insertInto($table, $fields); | |
362 | } | |
363 | ||
364 | /** | |
365 | * Wrapper function of insertInto fn but sets insertVerb = "REPLACE INTO " | |
366 | * | |
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). | |
371 | */ | |
be2fb01f | 372 | public function replaceInto($table, $fields = []) { |
6dd717a6 | 373 | $this->insertVerb = "REPLACE INTO "; |
374 | return $this->insertInto($table, $fields); | |
375 | } | |
376 | ||
0624cdc5 TO |
377 | /** |
378 | * Take the results of the SELECT query and copy them into another | |
379 | * table. | |
380 | * | |
381 | * If the same record already exists in the other table (based on | |
382 | * primary-key or unique-key), then update the corresponding record. | |
383 | * | |
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. | |
391 | * | |
392 | * For example, consider: | |
393 | * ['relationship_id' => 'rel.id'] | |
394 | * | |
395 | * This would select the value of 'rel.id' and write to 'relationship_id'. | |
396 | * | |
397 | * @param null|array $args | |
398 | * Use NULL to skip interpolation; use an array of variables to enable. | |
399 | * @return $this | |
400 | */ | |
401 | public function syncInto($table, $keys, $mapping, $args = NULL) { | |
402 | $keys = (array) $keys; | |
403 | ||
404 | $this->select(array_values($mapping), $args); | |
405 | $this->insertInto($table, array_keys($mapping)); | |
406 | ||
407 | foreach ($mapping as $intoColumn => $fromValue) { | |
408 | if (!in_array($intoColumn, $keys)) { | |
409 | $this->onDuplicate("$intoColumn = $fromValue", $args); | |
410 | } | |
411 | } | |
412 | ||
413 | return $this; | |
414 | } | |
415 | ||
c5458931 TO |
416 | /** |
417 | * @param array $fields | |
418 | * The fields to fill in the other table (in order). | |
14069c56 | 419 | * @return CRM_Utils_SQL_Select |
c5458931 TO |
420 | */ |
421 | public function insertIntoField($fields) { | |
422 | $fields = (array) $fields; | |
423 | foreach ($fields as $field) { | |
424 | $this->insertIntoFields[] = $field; | |
425 | } | |
426 | return $this; | |
427 | } | |
428 | ||
fbf5eca6 TO |
429 | /** |
430 | * For INSERT INTO...SELECT...' queries, you may give an "ON DUPLICATE UPDATE" clause. | |
431 | * | |
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 | |
435 | */ | |
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; | |
441 | } | |
442 | return $this; | |
443 | } | |
444 | ||
c5458931 | 445 | /** |
2024d5b9 | 446 | * @param array|null $parts |
c5458931 TO |
447 | * List of fields to check (e.g. 'selects', 'joins'). |
448 | * Defaults to all. | |
449 | * @return bool | |
450 | */ | |
451 | public function isEmpty($parts = NULL) { | |
452 | $empty = TRUE; | |
be2fb01f | 453 | $fields = [ |
c5458931 TO |
454 | 'insertInto', |
455 | 'insertIntoFields', | |
456 | 'selects', | |
457 | 'from', | |
458 | 'joins', | |
459 | 'wheres', | |
460 | 'groupBys', | |
461 | 'havings', | |
462 | 'orderBys', | |
463 | 'limit', | |
464 | 'offset', | |
be2fb01f | 465 | ]; |
c5458931 TO |
466 | if ($parts !== NULL) { |
467 | $fields = array_intersect($fields, $parts); | |
468 | } | |
469 | foreach ($fields as $field) { | |
470 | if (!empty($this->{$field})) { | |
471 | $empty = FALSE; | |
472 | } | |
473 | } | |
474 | return $empty; | |
475 | } | |
476 | ||
e2b5e5b1 | 477 | /** |
a6c01b45 CW |
478 | * @return string |
479 | * SQL statement | |
e2b5e5b1 TO |
480 | */ |
481 | public function toSQL() { | |
c5458931 TO |
482 | $sql = ''; |
483 | if ($this->insertInto) { | |
6dd717a6 | 484 | $sql .= $this->insertVerb . $this->insertInto . ' ('; |
c5458931 TO |
485 | $sql .= implode(', ', $this->insertIntoFields); |
486 | $sql .= ")\n"; | |
487 | } | |
6dd717a6 | 488 | |
e2b5e5b1 | 489 | if ($this->selects) { |
f8da3b93 | 490 | $sql .= 'SELECT ' . $this->distinct . implode(', ', $this->selects) . "\n"; |
e2b5e5b1 TO |
491 | } |
492 | else { | |
c5458931 TO |
493 | $sql .= 'SELECT *' . "\n"; |
494 | } | |
495 | if ($this->from !== NULL) { | |
496 | $sql .= 'FROM ' . $this->from . "\n"; | |
e2b5e5b1 | 497 | } |
e2b5e5b1 TO |
498 | foreach ($this->joins as $join) { |
499 | $sql .= $join . "\n"; | |
500 | } | |
501 | if ($this->wheres) { | |
502 | $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n"; | |
503 | } | |
504 | if ($this->groupBys) { | |
505 | $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n"; | |
506 | } | |
507 | if ($this->havings) { | |
508 | $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n"; | |
509 | } | |
510 | if ($this->orderBys) { | |
6db70618 | 511 | $orderBys = CRM_Utils_Array::crmArraySortByField($this->orderBys, |
be2fb01f | 512 | ['weight', 'guid']); |
4c6cc364 CW |
513 | $orderBys = CRM_Utils_Array::collect('value', $orderBys); |
514 | $sql .= 'ORDER BY ' . implode(', ', $orderBys) . "\n"; | |
e2b5e5b1 | 515 | } |
c3a8e3e5 TO |
516 | if ($this->limit !== NULL) { |
517 | $sql .= 'LIMIT ' . $this->limit . "\n"; | |
518 | if ($this->offset !== NULL) { | |
519 | $sql .= 'OFFSET ' . $this->offset . "\n"; | |
520 | } | |
521 | } | |
fbf5eca6 TO |
522 | if ($this->onDuplicates) { |
523 | if ($this->insertVerb === 'INSERT INTO ') { | |
524 | $sql .= ' ON DUPLICATE KEY UPDATE ' . implode(", ", $this->onDuplicates) . "\n"; | |
525 | } | |
526 | else { | |
527 | throw new \Exception("The ON DUPLICATE clause and only be used with INSERT INTO queries."); | |
528 | } | |
529 | } | |
530 | ||
c5458931 TO |
531 | if ($this->mode === self::INTERPOLATE_OUTPUT) { |
532 | $sql = $this->interpolate($sql, $this->params, self::INTERPOLATE_OUTPUT); | |
533 | } | |
e2b5e5b1 TO |
534 | return $sql; |
535 | } | |
96025800 | 536 | |
77e74ae1 | 537 | /** |
c4dcc9cf TO |
538 | * Execute the query. |
539 | * | |
540 | * To examine the results, use a function like `fetch()`, `fetchAll()`, | |
541 | * `fetchValue()`, or `fetchMap()`. | |
542 | * | |
2024d5b9 | 543 | * @param string|null $daoName |
c4dcc9cf TO |
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? | |
77e74ae1 | 549 | * @return CRM_Core_DAO |
c4dcc9cf TO |
550 | * @see CRM_Core_DAO::executeQuery |
551 | * @see CRM_Core_I18n_Schema::rewriteQuery | |
77e74ae1 TO |
552 | */ |
553 | public function execute($daoName = NULL, $i18nRewrite = TRUE) { | |
554 | // Don't pass through $params. toSQL() handles interpolation. | |
be2fb01f | 555 | $params = []; |
77e74ae1 TO |
556 | |
557 | // Don't pass through $abort, $trapException. Just use straight-up exceptions. | |
558 | $abort = TRUE; | |
559 | $trapException = FALSE; | |
77e74ae1 TO |
560 | |
561 | // Don't pass through freeDAO. You can do it yourself. | |
562 | $freeDAO = FALSE; | |
563 | ||
564 | return CRM_Core_DAO::executeQuery($this->toSQL(), $params, $abort, $daoName, | |
565 | $freeDAO, $i18nRewrite, $trapException); | |
566 | } | |
567 | ||
e2b5e5b1 | 568 | } |