MySQL group_concat() with both ORDER BY and SEPARATOR

699 views
Skip to first unread message

jjbrei...@gmail.com

unread,
Dec 15, 2020, 8:35:26 PM12/15/20
to sqlalchemy
I want to execute the following MySQL query:

  SELECT group_concat(DISTINCT lower(`role`.`name`) ORDER BY lower(`role`.`name`) SEPARATOR '|')
    FROM ...
GROUP BY ...

Using the latest stable release of SQLAlchemy (1.3.20), I've written the following code:

class Role(Base):
  __tablename__ = 'role'

  id = Column(BigInteger, primary_key=True)
  _created = Column(DateTime, nullable=False, server_default=text('CURRENT_TIMESTAMP'))
  _updated = Column(DateTime)
  _deleted = Column(Boolean, nullable=False, server_default=text("'0'"))
  name = Column(String(200), nullable=False)
  description = Column(String(1000))

result = Session.query()\
.add_columns(func.group_concat(func.lower(Role.name).distinct()
.op('ORDER BY')(func.lower(Role.name).asc())
.op('SEPARATOR')(literal_column("'|'")))) \
...

When executed, this emits an extra pair of parentheses in the statement which results in a SQL error:

SELECT group_concat((DISTINCT lower(`role`.name) ORDER BY lower(`role`.name) ASC) SEPARATOR '|') AS group_concat_1

How can I construct my SQLAlchemy query so it does not include the extra parantheses?

Thanks in advance!

Mike Bayer

unread,
Dec 15, 2020, 10:28:47 PM12/15/20
to noreply-spamdigest via sqlalchemy
as this involves a MySQL-specific syntax, the easiest way to just make this happen without any hassle is to use literal_column(), such as:

q1 = s.query(
    func.group_concat(
        literal_column(
            "DISTINCT lower(`role`.`name`) ORDER BY lower(`role`.`name`) SEPARATOR '|'"
        )
    )
).select_from(Role)

print(q1)

beyond that, the SQL in there is unusual enough that I would otherwise build a custom @compiles to handle it, and of course this is an operator that can be made available in the MySQL dialect if we can get quality contributions.

I've written out an example of the custom compiles version in a test script below which you can experiment with.


from sqlalchemy import BigInteger
from sqlalchemy import Boolean
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import func
from sqlalchemy import literal_column
from sqlalchemy import String
from sqlalchemy import text
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.sql.expression import ColumnElement

Base = declarative_base()


class Role(Base):
    __tablename__ = "role"

    id = Column(BigInteger, primary_key=True)
    _created = Column(
        DateTime, nullable=False, server_default=text("CURRENT_TIMESTAMP")
    )
    _updated = Column(DateTime)
    _deleted = Column(Boolean, nullable=False, server_default=text("'0'"))
    name = Column(String(200), nullable=False)
    description = Column(String(1000))


"""
SELECT group_concat(DISTINCT lower(`role`.`name`) ORDER BY lower(`role`.`name`) SEPARATOR '|')
    FROM ...
GROUP BY ...
"""

s = Session()

# approach #1, just use literal_column()
q1 = s.query(
    func.group_concat(
        literal_column(
            "DISTINCT lower(`role`.`name`) ORDER BY lower(`role`.`name`) SEPARATOR '|'"
        )
    )
).select_from(Role)

print(q1)

# approach #2, build a ColumnElement.
class GroupConcat(ColumnElement):
    def __init__(self, expr, distinct=False, order_by=None, separator="|"):
        self.expr = expr
        self.distinct = distinct
        self.order_by = order_by
        self.separator = separator

    # this slightly private attribute automatically exports the FROM elements,
    # you can also use select_from() in your query
    @property
    def _from_objects(self):
        return self.expr._from_objects


@compiles(GroupConcat)
def _compile_group_concat(element, compiler, **kw):
    return "GROUP_CONCAT(%s%s%s SEPARATOR '%s')" % (
        "DISTINCT " if element.distinct else "",
        compiler.process(element.expr, **kw),
        " ORDER BY %s" % compiler.process(element.order_by, **kw)
        if element.order_by is not None
        else "",
        element.separator,
    )


q2 = s.query(
    GroupConcat(
        func.lower(Role.name), distinct=True, order_by=func.lower(Role.name)
    )
)

print(q2)
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

Reply all
Reply to author
Forward
0 new messages