From 38f2f84a7875c3108d8268032282aae991587afd Mon Sep 17 00:00:00 2001
From: colemanw <coleman@civicrm.org>
Date: Wed, 15 Nov 2023 20:12:18 -0500
Subject: [PATCH] APIv4 - Add GROUP_FIRST aggregate function

This fills a gap in the MySql spec. It returns the first result of a GROUP_CONCAT set.
---
 Civi/Api4/Query/SqlFunction.php               |  9 ++-
 Civi/Api4/Query/SqlFunctionGROUP_FIRST.php    | 66 +++++++++++++++++++
 .../phpunit/api/v4/Action/SqlFunctionTest.php | 18 +++++
 3 files changed, 92 insertions(+), 1 deletion(-)
 create mode 100644 Civi/Api4/Query/SqlFunctionGROUP_FIRST.php

diff --git a/Civi/Api4/Query/SqlFunction.php b/Civi/Api4/Query/SqlFunction.php
index 44a0b097d5..6a901ad22a 100644
--- a/Civi/Api4/Query/SqlFunction.php
+++ b/Civi/Api4/Query/SqlFunction.php
@@ -118,6 +118,13 @@ abstract class SqlFunction extends SqlExpression {
     if (static::$dataType) {
       $dataType = static::$dataType;
     }
+    elseif (static::$category === self::CATEGORY_AGGREGATE) {
+      $exprArgs = $this->getArgs();
+      // If the first expression is a SqlFunction/SqlEquation, allow it to control the aggregate dataType
+      if (method_exists($exprArgs[0]['expr'][0], 'formatOutputValue')) {
+        $exprArgs[0]['expr'][0]->formatOutputValue($dataType, $values, $key);
+      }
+    }
     if (isset($values[$key]) && $this->suffix && $this->suffix !== 'id') {
       $dataType = 'String';
       $value =& $values[$key];
@@ -162,7 +169,7 @@ abstract class SqlFunction extends SqlExpression {
    * @return string
    */
   protected function renderExpression(string $output): string {
-    return $this->getName() . '(' . $output . ')';
+    return $this->getName() . "($output)";
   }
 
   /**
diff --git a/Civi/Api4/Query/SqlFunctionGROUP_FIRST.php b/Civi/Api4/Query/SqlFunctionGROUP_FIRST.php
new file mode 100644
index 0000000000..4fa363ad7e
--- /dev/null
+++ b/Civi/Api4/Query/SqlFunctionGROUP_FIRST.php
@@ -0,0 +1,66 @@
+<?php
+/*
+ +--------------------------------------------------------------------+
+ | Copyright CiviCRM LLC. All rights reserved.                        |
+ |                                                                    |
+ | This work is published under the GNU AGPLv3 license with some      |
+ | permitted exceptions and without any warranty. For full license    |
+ | and copyright information, see https://civicrm.org/licensing       |
+ +--------------------------------------------------------------------+
+ */
+
+namespace Civi\Api4\Query;
+
+/**
+ * Sql function returns the first item in a GROUP_CONCAT set (per the ORDER_BY param)
+ * @since 5.69
+ */
+class SqlFunctionGROUP_FIRST extends SqlFunction {
+
+  public $supportsExpansion = TRUE;
+
+  protected static $category = self::CATEGORY_AGGREGATE;
+
+  protected static function params(): array {
+    return [
+      [
+        'max_expr' => 1,
+        'must_be' => ['SqlField', 'SqlFunction', 'SqlEquation'],
+        'optional' => FALSE,
+      ],
+      [
+        'name' => 'ORDER BY',
+        'label' => ts('Order by'),
+        'max_expr' => 1,
+        'flag_after' => ['ASC' => ts('Ascending'), 'DESC' => ts('Descending')],
+        'must_be' => ['SqlField'],
+        'optional' => TRUE,
+      ],
+    ];
+  }
+
+  /**
+   * @return string
+   */
+  public static function getTitle(): string {
+    return ts('First');
+  }
+
+  /**
+   * @return string
+   */
+  public static function getDescription(): string {
+    return ts('First value in the grouping.');
+  }
+
+  /**
+   * Render the final expression
+   * @param string $output
+   * @return string
+   */
+  protected function renderExpression(string $output): string {
+    $sep = \CRM_Core_DAO::VALUE_SEPARATOR;
+    return "SUBSTRING_INDEX(GROUP_CONCAT($output SEPARATOR '$sep'), '$sep', 1)";
+  }
+
+}
diff --git a/tests/phpunit/api/v4/Action/SqlFunctionTest.php b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
index 232709eb0e..c8caac3335 100644
--- a/tests/phpunit/api/v4/Action/SqlFunctionTest.php
+++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
@@ -104,6 +104,24 @@ class SqlFunctionTest extends Api4TestBase implements TransactionalInterface {
     $this->assertContains('1, ' . $cid . ', 100.00', $agg['GROUP_CONCAT:financial_type_id_contact_id_total_amount']);
     $this->assertEquals([TRUE, TRUE, FALSE, FALSE], $agg['is_donation']);
     $this->assertEquals(['January', 'February', 'March', 'April'], $agg['months']);
+
+    // Test GROUP_FIRST
+    $agg = Contribution::get(FALSE)
+      ->addGroupBy('contact_id')
+      ->addWhere('contact_id', '=', $cid)
+      ->addSelect('GROUP_FIRST(financial_type_id:name ORDER BY id) AS financial_type_1')
+      ->addSelect("GROUP_FIRST((financial_type_id = 1) ORDER BY id) AS is_donation_1")
+      ->addSelect("GROUP_FIRST((financial_type_id = 1) ORDER BY id DESC) AS is_donation_4")
+      ->addSelect("GROUP_FIRST(MONTH(receive_date):label ORDER BY id) AS months")
+      ->addSelect('COUNT(*) AS count')
+      ->execute()
+      ->first();
+
+    $this->assertTrue(4 === $agg['count']);
+    $this->assertEquals('Donation', $agg['financial_type_1']);
+    $this->assertEquals('January', $agg['months']);
+    $this->assertTrue($agg['is_donation_1']);
+    $this->assertFalse($agg['is_donation_4']);
   }
 
   public function testGroupConcatUnique(): void {
-- 
2.25.1