Fix groupConcat and ordering on Oracle

Signed-off-by: Vitor Mattos <vitor@php.rio>

6 files changed, 12 insertions, 10 deletions

diff --git a/lib/private/Comments/Manager.php b/lib/private/Comments/Manager.php
index 2e9acb6ca24..410fcf26f4d 100644
--- a/lib/private/Comments/Manager.php
+++ b/lib/private/Comments/Manager.php
@@ -1210,20 +1210,21 @@ class Manager implements ICommentsManager {
 					$totalQuery->expr()->literal('":'),
 					$totalQuery->func()->count('id')
 				),
-				'total'
+				'colonseparatedvalue'
 			)
+			->selectAlias($totalQuery->func()->count('id'), 'total')
 			->from('reactions', 'r')
 			->where($totalQuery->expr()->eq('r.parent_id', $qb->createNamedParameter($parentId)))
 			->groupBy('r.reaction')
-			->orderBy($totalQuery->func()->count('id'), 'DESC')
+			->orderBy('total', 'DESC')
 			->setMaxResults(200);
 
 		$jsonQuery = $this->dbConn->getQueryBuilder();
 		$jsonQuery
 			->selectAlias(
-				$totalQuery->func()->concat(
+				$jsonQuery->func()->concat(
 					$jsonQuery->expr()->literal('{'),
-					$jsonQuery->func()->groupConcat('total'),
+					$jsonQuery->func()->groupConcat('colonseparatedvalue', ',', $jsonQuery->getColumnName('total') . ' DESC'),
 					$jsonQuery->expr()->literal('}')
 				),
 				'json'
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php
index 03630ea14a3..ba59f42ce5d 100644
--- a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php
+++ b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php
@@ -59,7 +59,7 @@ class FunctionBuilder implements IFunctionBuilder {
 		return new QueryFunction(sprintf('CONCAT(%s)', implode(', ', $list)));
 	}
 
-	public function groupConcat($expr, ?string $separator = ','): IQueryFunction {
+	public function groupConcat($expr, ?string $separator = ',', ?string $orderBy = null): IQueryFunction {
 		$separator = $this->connection->quote($separator);
 		return new QueryFunction('GROUP_CONCAT(' . $this->helper->quoteColumnName($expr) . ' SEPARATOR ' . $separator . ')');
 	}
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php
index 43ecf599eba..77f14a2dda4 100644
--- a/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php
+++ b/lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php
@@ -82,8 +82,8 @@ class OCIFunctionBuilder extends FunctionBuilder {
 		return new QueryFunction(sprintf('(%s)', implode(' || ', $list)));
 	}
 
-	public function groupConcat($expr, ?string $separator = ','): IQueryFunction {
-		$orderByClause = ' WITHIN GROUP(ORDER BY NULL)';
+	public function groupConcat($expr, ?string $separator = ',', ?string $orderBy = 'NULL'): IQueryFunction {
+		$orderByClause = ' WITHIN GROUP(ORDER BY ' . $orderBy . ')';
 		if (is_null($separator)) {
 			return new QueryFunction('LISTAGG(' . $this->helper->quoteColumnName($expr) . ')' . $orderByClause);
 		}
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php
index 444f6aa83a4..3a1d9a56734 100644
--- a/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php
+++ b/lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php
@@ -37,7 +37,7 @@ class PgSqlFunctionBuilder extends FunctionBuilder {
 		return new QueryFunction(sprintf('(%s)', implode(' || ', $list)));
 	}
 
-	public function groupConcat($expr, ?string $separator = ','): IQueryFunction {
+	public function groupConcat($expr, ?string $separator = ',', ?string $orderBy = null): IQueryFunction {
 		$castedExpression = $this->queryBuilder->expr()->castColumn($expr, IQueryBuilder::PARAM_STR);
 
 		if (is_null($separator)) {
diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php
index fe700075a82..cfcabc9eb1a 100644
--- a/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php
+++ b/lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php
@@ -36,7 +36,7 @@ class SqliteFunctionBuilder extends FunctionBuilder {
 		return new QueryFunction(sprintf('(%s)', implode(' || ', $list)));
 	}
 
-	public function groupConcat($expr, ?string $separator = ','): IQueryFunction {
+	public function groupConcat($expr, ?string $separator = ',', ?string $orderBy = null): IQueryFunction {
 		$separator = $this->connection->quote($separator);
 		return new QueryFunction('GROUP_CONCAT(' . $this->helper->quoteColumnName($expr) . ', ' . $separator . ')');
 	}
diff --git a/lib/public/DB/QueryBuilder/IFunctionBuilder.php b/lib/public/DB/QueryBuilder/IFunctionBuilder.php
index 04c5cbd07bd..9b0c7254250 100644
--- a/lib/public/DB/QueryBuilder/IFunctionBuilder.php
+++ b/lib/public/DB/QueryBuilder/IFunctionBuilder.php
@@ -62,10 +62,11 @@ interface IFunctionBuilder {
 	 *
 	 * @param string|IQueryFunction $expr The expression to group
 	 * @param string|null $separator The separator
+	 * @param string|null $orderBy Option only used to make compatible with Oracle database if is necessary use order. The default value is null and the Oracle don't will respect the order by of query
 	 * @return IQueryFunction
 	 * @since 24.0.0
 	 */
-	public function groupConcat($expr, ?string $separator = ','): IQueryFunction;
+	public function groupConcat($expr, ?string $separator = ',', ?string $orderBy = null): IQueryFunction;
 
 	/**
 	 * Takes a substring from the input string