From 744e1be1b31e02dcb0caa1175d36d74c996f370c Mon Sep 17 00:00:00 2001 From: Joshua Morris Date: Sun, 4 Jun 2023 19:52:21 +1000 Subject: [PATCH] Add support for `string_agg` with MySQL backend --- lib/sqlalchemy/dialects/mysql/base.py | 9 +++++++++ lib/sqlalchemy/sql/elements.py | 3 +++ test/sql/test_functions.py | 8 ++++++++ 3 files changed, 20 insertions(+) diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 0a68b3779e..4712c9b402 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1208,6 +1208,15 @@ class MySQLCompiler(compiler.SQLCompiler): ) return f"{clause} WITH ROLLUP" + def visit_string_agg_func(self, fn, **kw): + if len(fn.clauses) > 1: + return "group_concat(%s SEPARATOR %s)" % ( + fn.clauses[0]._compiler_dispatch(self, **kw), + fn.clauses[-1]._compiler_dispatch(self, **kw), + ) + else: + return "group_concat%s" % self.function_argspec(fn) + def visit_sequence(self, seq, **kw): return "nextval(%s)" % self.preparer.format_sequence(seq) diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index ba074db80c..ad5fcde1ae 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -2766,6 +2766,9 @@ class ClauseList( self._is_implicitly_boolean = False return self + def __getitem__(self, item) -> ColumnElement[Any]: + return self.clauses[item] + def __iter__(self) -> Iterator[ColumnElement[Any]]: return iter(self.clauses) diff --git a/test/sql/test_functions.py b/test/sql/test_functions.py index 51ed638940..c15b763888 100644 --- a/test/sql/test_functions.py +++ b/test/sql/test_functions.py @@ -242,6 +242,14 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): literal_binds=True, render_postcompile=True, ) + self.assert_compile( + stmt, + "SELECT group_concat(t.value SEPARATOR ',') " + "AS string_agg_1 FROM t", + dialect=mysql.dialect(), + literal_binds=True, + render_postcompile=True, + ) def test_cube_operators(self): t = table( -- 2.47.3