From 77e74ae16f50b859ce94d66d4b7a890075adba54 Mon Sep 17 00:00:00 2001
From: Tim Otten <totten@civicrm.org>
Date: Mon, 17 Jul 2017 16:17:04 -0700
Subject: [PATCH] CRM_Utils_SQL_Select - Allow fluent query execution

When I first wrote `CRM_Utils_SQL_Select`, I was a bit dogmatic about
loose-coupling and wanted the class to be entirely independent of the SQL
runtime. But this is a bit annoying in usage and training.

Before
======

To build and execute query, you had to pass the rendered SQL to the execute
function, eg

```php
$select = CRM_Utils_SQL_Select::from('mytable')
  ->select('...')
$dao = CRM_Core_DAO::executeQuery($select->toSQL());
while ($dao->fetch()) { ... }
```

After
=====

You can use more fluent style:

```php
$dao = CRM_Utils_SQL_Select::from('mytable')
  ->select('...')
  ->execute();
while ($dao->fetch()) { ... }
```

And you can chain with other DAO functions like `fetchAll()` or
`fetchValue()`.

```php
$records = CRM_Utils_SQL_Select::from('mytable')
  ->select('...')
  ->execute()
  ->fetchAll();
```
---
 CRM/Core/DAO.php                           | 16 +++++++++++
 CRM/Utils/SQL/Select.php                   | 19 +++++++++++++
 tests/phpunit/CRM/Utils/SQL/SelectTest.php | 33 ++++++++++++++++++++++
 3 files changed, 68 insertions(+)

diff --git a/CRM/Core/DAO.php b/CRM/Core/DAO.php
index b1bf5ba765..9d8c5ab2f6 100644
--- a/CRM/Core/DAO.php
+++ b/CRM/Core/DAO.php
@@ -1032,6 +1032,22 @@ FROM   civicrm_domain
     return $result;
   }
 
+  /**
+   * Returns a singular value.
+   *
+   * @return mixed|NULL
+   */
+  public function fetchValue() {
+    $result = $this->getDatabaseResult();
+    $row = $result->fetchRow();
+    $ret = NULL;
+    if ($row) {
+      $ret = $row[0];
+    }
+    $this->free();
+    return $ret;
+  }
+
   /**
    * Get all the result records as mapping between columns.
    *
diff --git a/CRM/Utils/SQL/Select.php b/CRM/Utils/SQL/Select.php
index 54128213d6..eccdbadb15 100644
--- a/CRM/Utils/SQL/Select.php
+++ b/CRM/Utils/SQL/Select.php
@@ -593,6 +593,25 @@ class CRM_Utils_SQL_Select implements ArrayAccess {
     return $sql;
   }
 
+  /**
+   * @return CRM_Core_DAO
+   */
+  public function execute($daoName = NULL, $i18nRewrite = TRUE) {
+    // Don't pass through $params. toSQL() handles interpolation.
+    $params = array();
+
+    // Don't pass through $abort, $trapException. Just use straight-up exceptions.
+    $abort = TRUE;
+    $trapException = FALSE;
+    $errorScope = CRM_Core_TemporaryErrorScope::useException();
+
+    // Don't pass through freeDAO. You can do it yourself.
+    $freeDAO = FALSE;
+
+    return CRM_Core_DAO::executeQuery($this->toSQL(), $params, $abort, $daoName,
+      $freeDAO, $i18nRewrite, $trapException);
+  }
+
   /**
    * Has an offset been set.
    *
diff --git a/tests/phpunit/CRM/Utils/SQL/SelectTest.php b/tests/phpunit/CRM/Utils/SQL/SelectTest.php
index f7022411d3..4f6368683d 100644
--- a/tests/phpunit/CRM/Utils/SQL/SelectTest.php
+++ b/tests/phpunit/CRM/Utils/SQL/SelectTest.php
@@ -10,6 +10,39 @@ class CRM_Utils_SQL_SelectTest extends CiviUnitTestCase {
     $this->assertLike('SELECT * FROM foo bar', $select->toSQL());
   }
 
+  public function testExecute_OK() {
+    // We need some SQL query.
+    $select = CRM_Utils_SQL_Select::from('civicrm_contact')
+      ->select('count(*) as cnt');
+    $this->assertLike('SELECT count(*) as cnt FROM civicrm_contact', $select->toSQL());
+
+    // Try with typical fetch().
+    $rows = 0;
+    $dao = $select->execute();
+    while ($dao->fetch()) {
+      $rows++;
+      $this->assertTrue(is_numeric($dao->cnt), "Expect query to execute");
+    }
+    $this->assertEquals(1, $rows);
+
+    // Try with fetchValue().
+    $this->assertTrue(is_numeric($select->execute()->fetchValue()));
+
+    // Try with fetchAll()
+    $records = $select->execute()->fetchAll();
+    $this->assertTrue(is_numeric($records[0]['cnt']));
+  }
+
+  public function testExecute_Error() {
+    try {
+      CRM_Utils_SQL_Select::from('civicrm_contact')->select('snarb;barg')->execute();
+      $this->fail('Expected an exception');
+    }
+    catch (PEAR_Exception $e) {
+      $this->assertTrue(TRUE, "Received expected exception");
+    }
+  }
+
   public function testGetFields() {
     $select = CRM_Utils_SQL_Select::from('foo')
       ->select('bar')
-- 
2.25.1