From 0624cdc58ffbbce9dcdeb521ab362129d645b9be Mon Sep 17 00:00:00 2001
From: Tim Otten <totten@civicrm.org>
Date: Wed, 8 Jul 2020 22:52:29 -0700
Subject: [PATCH] CRM_Utils_SQL - Add "syncInto()" helper

This is slightly more sugary variant of "INSERT INTO...SELECT...ON DUPLICATE UPDATE..." which requires
less boilerplate/duplication.

Before
------

```php
CRM_Utils_SQL_Select::from('foo_table')
  ->select(['foo_name', 'foo_value1', 'foo_value2'])
  ->insertInto('bar_table', ['bar_name', 'bar_output1', 'bar_output2'])
  ->onDuplicate(['bar_output1 = foo_value1', 'bar_output2 = foo_value2'])
```

After
------

```php
CRM_Utils_SQL_Select::from('foo_table')
  ->syncInto('bar_table', 'bar_name', [
    'bar_name' => 'foo_name',
    'bar_output1' => 'foo_value1',
    'bar_output2' => 'foo_value1',
  ])
```
---
 CRM/Utils/SQL/Select.php                   | 39 ++++++++++++++++++++++
 tests/phpunit/CRM/Utils/SQL/SelectTest.php | 25 ++++++++++++++
 2 files changed, 64 insertions(+)

diff --git a/CRM/Utils/SQL/Select.php b/CRM/Utils/SQL/Select.php
index 44dcf49dbd..642d98ff2b 100644
--- a/CRM/Utils/SQL/Select.php
+++ b/CRM/Utils/SQL/Select.php
@@ -374,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).
diff --git a/tests/phpunit/CRM/Utils/SQL/SelectTest.php b/tests/phpunit/CRM/Utils/SQL/SelectTest.php
index f28d4ccbcf..74270a9468 100644
--- a/tests/phpunit/CRM/Utils/SQL/SelectTest.php
+++ b/tests/phpunit/CRM/Utils/SQL/SelectTest.php
@@ -334,6 +334,31 @@ class CRM_Utils_SQL_SelectTest extends CiviUnitTestCase {
     $this->assertLike('INSERT INTO bar (first, second, third) SELECT foo.one, foo.two, foo.three FROM foo ON DUPLICATE KEY UPDATE second = twiddle(foo.two), third = twiddle(foo.three)', $select->toSQL());
   }
 
+  public function testSyncInto_EarlyInterpolate() {
+    $select = CRM_Utils_SQL_Select::from('foo')
+      ->where('foo.whiz = 100')
+      ->syncInto('bar', ['name'], [
+        'name' => 'foo.name',
+        'first' => 'concat(foo.one, @suffix)',
+        'second' => 'foo.two',
+      ], [
+        '@suffix' => ' and more',
+      ]);
+    $this->assertLike('INSERT INTO bar (name, first, second) SELECT foo.name, concat(foo.one, " and more"), foo.two FROM foo WHERE (foo.whiz = 100) ON DUPLICATE KEY UPDATE first = concat(foo.one, " and more"), second = foo.two', $select->toSQL());
+  }
+
+  public function testSyncInto_LateInterpolate() {
+    $select = CRM_Utils_SQL_Select::from('foo')
+      ->where('foo.whiz = 100')
+      ->syncInto('bar', ['name'], [
+        'name' => 'foo.name',
+        'first' => 'concat(foo.one, @suffix)',
+        'second' => 'foo.two',
+      ])
+      ->param(['@suffix' => ' and on and on']);
+    $this->assertLike('INSERT INTO bar (name, first, second) SELECT foo.name, concat(foo.one, " and on and on"), foo.two FROM foo WHERE (foo.whiz = 100) ON DUPLICATE KEY UPDATE first = concat(foo.one, " and on and on"), second = foo.two', $select->toSQL());
+  }
+
   /**
    * @param $expected
    * @param $actual
-- 
2.25.1