如何在SQLAlchemy中编写group_concat函数？

Question

我将尝试在SQLAlchemy中重新编写此MySQL查询：

架构：

CREATE TABLE `posts` (
    `post_id` INT UNSIGNED PRIMARY AUTO_INCREMENT,
    `post_name` VARCHAR(255)
) Engine=InnoDB;

CREATE TABLE `post_tags` (
    `tag_id` INT UNSIGNED PRIMARY AUTO_INCREMENT,
    `tag_name` VARCHAR(255)
) Engine=InnoDB;

CREATE TABLE `post_tags_map` (
    `map_id` INT PRIMARY AUTO_INCREMENT,
    `post_id` INT NOT NULL,
    `tags_id` INT NOT NULL,
    FOREIGN KEY `post_id` REFERENCES `posts` (`post_id`),
    FOREIGN KEY `post_id` REFERENCES `post_tags` (`tag_id`)
) Engine=InnoDB;

查询：

SELECT 
    posts.*,
    GROUP_CONCAT( post_tags.tag_name order by post_tags.tag_name ) AS tags

  FROM posts
    LEFT JOIN posts_tags_map
      ON posts_tags_map.post_id = posts.post_id
    LEFT JOIN post_tags
      ON posts_tags_map.tags_id = posts_tags.tag_id

  WHERE posts.post_id = 1
  GROUP BY post_id

这是我拥有的内容，但我一直得到的是：

1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEPARATOR /))

rows = DBSession.query(Posts,func.group_concat(Post_Tags.tag_name.op('SEPARATOR')(literal_column('/')))).outerjoin(PostsTagsMap,Posts.post_id==PostsTagsMap.post_id).outerjoin(Post_Tags,PostsTagsMap.tags_id==Post_Tags.tag_id).group_by(Posts.post_id)

- user776942

Answer 1

浪费了很多时间测试上面的示例和其他随机来源，以找出如何更改func.group_concat()中的标准分隔符","...只是随后跟随直觉并发现语法的工作方式与您预期的完全相同：将您的分隔符作为参数传递。 例如：

from sqlalchemy import func
#write the query: db.session.query(...
...func.group_concat(Table.column_name, "; ")

因此，修改之前答案的示例：

DBSession.query(
   Posts, 
   func.group_concat(Post_Tags.tag_name, "/"))<...>

注意：这是使用Python 3.5、SQLite 3和Flask-SQLAlchemy 2.3.2（安装了SQLAlchemy 1.0.13）的情况。

- GAINZ

---

无效。它会“编译”成 GROUP_CONCAT(name, "; ")，这实际上会将“;”附加到每个记录，但保留默认的“,”分隔符。 - Romuald Brunet

Answer 2

实际上，你正确地执行了group_concat这一部分：

DBSession.query(
   Posts, 
   func.group_concat(Post_Tags.tag_name.op('SEPARATOR')(literal_column('/'))))<...>

我不确定具体情况，因为您的模式无效，查询中有拼写错误的表名，而且根据错误来看，您尝试将Python代码输入MySQL。对于像我一样通过谷歌搜索而来的人，上面的代码段应该是有帮助的，此外还有源代码：https://groups.google.com/forum/#!msg/sqlalchemy/eNbOoRJ425s/bScfQVat15EJ。

- thule

---

这里似乎需要使用 literal_column 函数，可以通过 .op('separator')('/') 来实现。 - Romuald Brunet

literal_column 不会对分隔符进行引用，因此在 MariaDB 上至少会导致语法错误。将分隔符作为 '/' 或 literal('/') 传递都会产生语法上正确的语句。 - snakecharmerb

Answer 3

我终于让它工作了。

>>> str(func.group_concat(Keyword.text.op('separator')(text('","'))))
'group_concat(keywords.text separator ",")'

- rexsheng

Answer 4

定义一个自定义函数元素似乎是处理group_concat方法最简单的方法。

from sqlalchemy.sql import expression
import sqlalchemy
from sqlalchemy.ext import compiler


class group_concat(expression.FunctionElement):
    name = "group_concat"


@compiler.compiles(group_concat, 'mysql')
def _group_concat_mysql(element, compiler, **kw):
    if len(element.clauses) == 2:
        separator = compiler.process(element.clauses.clauses[1])
    else:
        separator = ','

    return 'GROUP_CONCAT(%s SEPARATOR %s)'.format(
        compiler.process(element.clauses.clauses[0]),
        separator,
    )

然后像这样使用它：

query = select([
    table.c.some_column,
    expression.label(
        'grouped column',
        group_concat(
            table.c.some_oter_column,
            ' separator ',
        ),
    ),
]).group_by(table.c.some_column)

- Wolph

Answer 5

我尝试使用literal_column(' = ')，但MYSQL会将其解析为separator =而不是separator '='，所以我在前后添加三个引号，变成literal_column("""' = '""")，最终成功了。

- Jared

---

请查看此评论。 - snakecharmerb