From afe5b6dd8aa9a0eb761434231327e8daecb1d79e Mon Sep 17 00:00:00 2001 From: Vitor Mattos Date: Tue, 11 Jan 2022 12:20:13 -0300 Subject: Prevent query error when use subquery Signed-off-by: Vitor Mattos --- lib/public/DB/QueryBuilder/IQueryBuilder.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'lib/public/DB') diff --git a/lib/public/DB/QueryBuilder/IQueryBuilder.php b/lib/public/DB/QueryBuilder/IQueryBuilder.php index 7829696970c..669003246d9 100644 --- a/lib/public/DB/QueryBuilder/IQueryBuilder.php +++ b/lib/public/DB/QueryBuilder/IQueryBuilder.php @@ -470,7 +470,7 @@ interface IQueryBuilder { * ->from('users', 'u') * * - * @param string $from The table. + * @param string|IQueryFunction $from The table. * @param string|null $alias The alias of the table. * * @return $this This QueryBuilder instance. @@ -994,7 +994,7 @@ interface IQueryBuilder { /** * Returns the table name quoted and with database prefix as needed by the implementation * - * @param string $table + * @param string|IQueryFunction $table * @return string * @since 9.0.0 */ -- cgit v1.2.3 From f071b4dfbbd5fccae9b7b07b9a13ed71ddc91ce4 Mon Sep 17 00:00:00 2001 From: Vitor Mattos Date: Fri, 14 Jan 2022 15:56:47 -0300 Subject: Fix groupConcat and ordering on Oracle Signed-off-by: Vitor Mattos --- lib/private/Comments/Manager.php | 9 +++++---- lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php | 2 +- .../DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php | 4 ++-- .../DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php | 2 +- .../DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php | 2 +- lib/public/DB/QueryBuilder/IFunctionBuilder.php | 3 ++- 6 files changed, 12 insertions(+), 10 deletions(-) (limited to 'lib/public/DB') 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 -- cgit v1.2.3 From d850dc02207769ef13897867a11083e420f115c4 Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Mon, 17 Jan 2022 11:58:50 +0100 Subject: Remove order from groupConcat as it is not working everywhere Signed-off-by: Joas Schilling --- lib/private/Comments/Manager.php | 2 +- lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php | 2 +- lib/private/DB/QueryBuilder/FunctionBuilder/OCIFunctionBuilder.php | 4 ++-- lib/private/DB/QueryBuilder/FunctionBuilder/PgSqlFunctionBuilder.php | 2 +- lib/private/DB/QueryBuilder/FunctionBuilder/SqliteFunctionBuilder.php | 2 +- lib/public/DB/QueryBuilder/IFunctionBuilder.php | 3 +-- 6 files changed, 7 insertions(+), 8 deletions(-) (limited to 'lib/public/DB') diff --git a/lib/private/Comments/Manager.php b/lib/private/Comments/Manager.php index 95fb7f24e53..a4afffe2cd4 100644 --- a/lib/private/Comments/Manager.php +++ b/lib/private/Comments/Manager.php @@ -1233,7 +1233,7 @@ class Manager implements ICommentsManager { ->selectAlias( $jsonQuery->func()->concat( $jsonQuery->expr()->literal('{'), - $jsonQuery->func()->groupConcat('colonseparatedvalue', ','), + $jsonQuery->func()->groupConcat('colonseparatedvalue'), $jsonQuery->expr()->literal('}') ), 'json' diff --git a/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php b/lib/private/DB/QueryBuilder/FunctionBuilder/FunctionBuilder.php index ba59f42ce5d..03630ea14a3 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 = ',', ?string $orderBy = null): IQueryFunction { + public function groupConcat($expr, ?string $separator = ','): 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 77f14a2dda4..43ecf599eba 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 = ',', ?string $orderBy = 'NULL'): IQueryFunction { - $orderByClause = ' WITHIN GROUP(ORDER BY ' . $orderBy . ')'; + public function groupConcat($expr, ?string $separator = ','): IQueryFunction { + $orderByClause = ' WITHIN GROUP(ORDER BY NULL)'; 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 3a1d9a56734..444f6aa83a4 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 = ',', ?string $orderBy = null): IQueryFunction { + public function groupConcat($expr, ?string $separator = ','): 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 cfcabc9eb1a..fe700075a82 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 = ',', ?string $orderBy = null): IQueryFunction { + public function groupConcat($expr, ?string $separator = ','): 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 9b0c7254250..04c5cbd07bd 100644 --- a/lib/public/DB/QueryBuilder/IFunctionBuilder.php +++ b/lib/public/DB/QueryBuilder/IFunctionBuilder.php @@ -62,11 +62,10 @@ 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 = ',', ?string $orderBy = null): IQueryFunction; + public function groupConcat($expr, ?string $separator = ','): IQueryFunction; /** * Takes a substring from the input string -- cgit v1.2.3