Merge pull request #18171 from demeritcowboy/unused-UTF8

[civicrm-core.git] / CRM / Utils / SQL / Select.php

diff --git a/CRM/Utils/SQL/Select.php b/CRM/Utils/SQL/Select.php
index 4aa6b7e5166974cd334df51fb6b1f27c0dd986b5..642d98ff2b8b7d82c4a53c086186c05632be7b7b 100644 (file)
--- a/CRM/Utils/SQL/Select.php
+++ b/CRM/Utils/SQL/Select.php
@@ -69,6 +69,7 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery {
   private $insertInto = NULL;
   private $insertVerb = 'INSERT INTO ';
   private $insertIntoFields = [];
+  private $onDuplicates = [];
   private $selects = [];
   private $from;
   private $joins = [];
@@ -373,6 +374,45 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery {
     return $this->insertInto($table, $fields);
   }
 
+  /**
+   * Take the results of the SELECT query and copy them into another
+   * table.
+   *
+   * If the same record already exists in the other table (based on
+   * primary-key or unique-key), then update the corresponding record.
+   *
+   * @param string $table
+   *   The table to write data into.
+   * @param array|string $keys
+   *   List of PK/unique fields
+   *   NOTE: This must match the unique-key that was declared in the schema.
+   * @param array $mapping
+   *   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'.
+   *
+   * @param null|array $args
+   *   Use NULL to skip interpolation; use an array of variables to enable.
+   * @return $this
+   */
+  public function syncInto($table, $keys, $mapping, $args = NULL) {
+    $keys = (array) $keys;
+
+    $this->select(array_values($mapping), $args);
+    $this->insertInto($table, array_keys($mapping));
+
+    foreach ($mapping as $intoColumn => $fromValue) {
+      if (!in_array($intoColumn, $keys)) {
+        $this->onDuplicate("$intoColumn = $fromValue", $args);
+      }
+    }
+
+    return $this;
+  }
+
   /**
    * @param array $fields
    *   The fields to fill in the other table (in order).
@@ -386,6 +426,22 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery {
     return $this;
   }
 
+  /**
+   * For INSERT INTO...SELECT...' queries, you may give an "ON DUPLICATE UPDATE" clause.
+   *
+   * @param string|array $exprs list of SQL expressions
+   * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
+   * @return CRM_Utils_SQL_Select
+   */
+  public function onDuplicate($exprs, $args = NULL) {
+    $exprs = (array) $exprs;
+    foreach ($exprs as $expr) {
+      $evaluatedExpr = $this->interpolate($expr, $args);
+      $this->onDuplicates[$evaluatedExpr] = $evaluatedExpr;
+    }
+    return $this;
+  }
+
   /**
    * @param array|NULL $parts
    *   List of fields to check (e.g. 'selects', 'joins').
@@ -463,6 +519,15 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery {
         $sql .= 'OFFSET ' . $this->offset . "\n";
       }
     }
+    if ($this->onDuplicates) {
+      if ($this->insertVerb === 'INSERT INTO ') {
+        $sql .= ' ON DUPLICATE KEY UPDATE ' . implode(", ", $this->onDuplicates) . "\n";
+      }
+      else {
+        throw new \Exception("The ON DUPLICATE clause and only be used with INSERT INTO queries.");
+      }
+    }
+
     if ($this->mode === self::INTERPOLATE_OUTPUT) {
       $sql = $this->interpolate($sql, $this->params, self::INTERPOLATE_OUTPUT);
     }