Use ordered option in ArrayMergeBuilder (#390)

Tigrov · web-flow · commit 31191b739b7c · 2025-08-26T18:54:14.000+07:00
diff --git a/CHANGELOG.md b/CHANGELOG.md
@@ -49,7 +49,7 @@
 - Enh #377: Remove `TableSchema` class and refactor `Schema` class (@Tigrov)
 - Enh #380: Support column's collation (@Tigrov)
 - New #385: Add `Connection::getColumnBuilderClass()` method (@Tigrov)
-- New #384: Implement `ArrayMergeBuilder`, `GreatestBuilder` and `LeastBuilder` classes (@Tigrov)
+- New #384, #390: Implement `ArrayMergeBuilder`, `GreatestBuilder` and `LeastBuilder` classes (@Tigrov)
 - Enh #387: Refactor `DMLQueryBuilder::upsert()` method (@Tigrov)
 
 ## 1.2.0 March 21, 2024
diff --git a/src/Builder/ArrayMergeBuilder.php b/src/Builder/ArrayMergeBuilder.php
@@ -43,6 +43,12 @@ protected function buildFromExpression(MultiOperandFunction $expression, array &
             $selects[] = "SELECT value FROM json_each($builtOperand)";
         }
 
-        return '(SELECT json_group_array(value) AS value FROM (' . implode(' UNION ', $selects) . '))';
+        $unions = implode(' UNION ', $selects);
+
+        if ($expression->getOrdered()) {
+            $unions .= ' ORDER BY value';
+        }
+
+        return '(SELECT json_group_array(value) AS value FROM (' . $unions . '))';
     }
 }
diff --git a/tests/QueryBuilderTest.php b/tests/QueryBuilderTest.php
@@ -8,9 +8,11 @@
 use PHPUnit\Framework\Attributes\DataProviderExternal;
 use Yiisoft\Db\Constant\DataType;
 use Yiisoft\Db\Exception\NotSupportedException;
+use Yiisoft\Db\Expression\ArrayExpression;
 use Yiisoft\Db\Expression\CaseExpression;
 use Yiisoft\Db\Expression\Expression;
 use Yiisoft\Db\Expression\ExpressionInterface;
+use Yiisoft\Db\Expression\Function\ArrayMerge;
 use Yiisoft\Db\Expression\Param;
 use Yiisoft\Db\Query\Query;
 use Yiisoft\Db\Query\QueryInterface;
@@ -19,6 +21,7 @@
 use Yiisoft\Db\Sqlite\Tests\Provider\QueryBuilderProvider;
 use Yiisoft\Db\Sqlite\Tests\Support\TestTrait;
 use Yiisoft\Db\Tests\Common\CommonQueryBuilderTest;
+use Yiisoft\Db\Tests\Support\Assert;
 
 /**
  * @group sqlite
@@ -746,4 +749,41 @@ public function testMultiOperandFunctionBuilderWithoutOperands(string $class): v
     {
         parent::testMultiOperandFunctionBuilderWithoutOperands($class);
     }
+
+    public function testArrayMergeWithOrdering(): void
+    {
+        $db = $this->getConnection();
+        $qb = $db->getQueryBuilder();
+
+        $stringParam = new Param('[4,3,5]', DataType::STRING);
+        $arrayMerge = (new ArrayMerge(
+            "'[2,1,3]'",
+            [6, 5, 7],
+            $stringParam,
+            self::getDb()->select(new ArrayExpression([10, 9])),
+        ))->ordered();
+        $params = [];
+
+        $this->assertSame(
+            '(SELECT json_group_array(value) AS value FROM ('
+            . "SELECT value FROM json_each('[2,1,3]')"
+            . ' UNION SELECT value FROM json_each(:qp0)'
+            . ' UNION SELECT value FROM json_each(:qp1)'
+            . ' UNION SELECT value FROM json_each((SELECT :qp2))'
+            . ' ORDER BY value))',
+            $qb->buildExpression($arrayMerge, $params)
+        );
+        Assert::arraysEquals(
+            [
+                ':qp0' => new Param('[6,5,7]', DataType::STRING),
+                ':qp1' => $stringParam,
+                ':qp2' => new Param('[10,9]', DataType::STRING),
+            ],
+            $params,
+        );
+
+        $result = $db->select($arrayMerge)->scalar();
+
+        $this->assertEquals('[1,2,3,4,5,6,7,9,10]', $result);
+    }
 }

Original file line number	Diff line number	Diff line change
`@@ -43,6 +43,12 @@ protected function buildFromExpression(MultiOperandFunction $expression, array &`
`43`	`43`	`$selects[] = "SELECT value FROM json_each($builtOperand)";`
`44`	`44`	`}`
`45`	`45`
`46`		`- return '(SELECT json_group_array(value) AS value FROM (' . implode(' UNION ', $selects) . '))';`
	`46`	`+ $unions = implode(' UNION ', $selects);`
	`47`	`+`
	`48`	`+ if ($expression->getOrdered()) {`
	`49`	`+ $unions .= ' ORDER BY value';`
	`50`	`+ }`
	`51`	`+`
	`52`	`+ return '(SELECT json_group_array(value) AS value FROM (' . $unions . '))';`
`47`	`53`	`}`
`48`	`54`	`}`