GROUP_CONCAT for Django ORM

Aggregate functions in Django ORM are cool. This circumstance was the reason to add one more =)

Next, we will talk about a mysql-specific function GROUP_CONCATand magic pink ponies, as in the django-trunk picture .


The functional that interests us is in two modules, django.db.models.aggregatesand django.db.models.sql.aggregates. So the scene is models.py:

from django.db import models
## aggregate functions ##
from django.db.models.aggregates import Aggregate # definition
from django.db.models.sql.aggregates import Aggregate as SQLAggregate # implementation

Let's start with the most interesting - the actual implementation.

class SQLConcat (SQLAggregate):
    sql_function = "GROUP_CONCAT"
    def __init __ (self, col, separator = ',', ** extra):
        self.sql_template = "%% (function) s (%% (field) s SEPARATOR '% s')"% separator
        super (SQLConcat, self) .__ init __ (col, source = models.DecimalField (), ** extra)

There is (at least) one ugly thing - passing models.DecimalField()to the parent constructor; this is necessary so that subsequently the result of the work of our function is not erroneously converted to a numerical type (!). This code will break when you next update the Django trunk or not, we will see after the next update of the Django trunk. You have been warned.

The implementation is ready, let's move on to the boring definition of a function:

class Concat (Aggregate):
    name = "Concat"
    def add_to_query (self, query, alias, col, source, is_summary):
        aggregate = SQLConcat (col, is_summary = is_summary, ** self.extra)
        query.aggregates [alias] = aggregate

It remains to make sure that our work works:

>>> Post.threads.aggregate (Concat ('id'))
{'id__concat': '1,2,3,4'}
>>> from django.db import connection
>>> connection.queries
[{'sql': u "SELECT GROUP_CONCAT (` main_post`.`id` SEPARATOR ',') AS `id__concat` FROM` main_post` ",
  'time': '0.000'}]

_{The first post on Habré, uiii!}