GROUP_CONCAT in ORM

Using Django Using the ORM
1

I have a raw SQL:

SELECT table1.field1,

(SELECT GROUP_CONCAT(commaSeparatedField SEPARATOR ', ')
FROM table2 WHERE table2.field12=field12) as commaSeparatedField

FROM table1

How do I do this in Django ORM ?

1 Like
2

Two thoughts come to mind here:

  1. Use a raw sql query.

  2. Take a step back and look at why you’re doing this - what the fundamental objective is for the output from this query, then re-evaluate your approach based on Django’s model objects and the ORM to determine what the “Django-style approach” would be within the context of your application’s models.

Not every solution is a direct translation from SQL to the ORM. It’s kinda like translating languages - not every language idiom is a word-for-work conversion. You want to focus on the meaning of what you’re trying to say.

(The “five whys” approach may be useful in cases like this.)

1 Like
3

The following should do

from django.db.models import Aggregate, CharField

# From https://stackoverflow.com/questions/10340684/group-concat-equivalent-in-django
class GroupConcat(Aggregate):
    function = 'GROUP_CONCAT'
    template = '%(function)s(%(distinct)s%(expressions)s%(ordering)s%(separator)s)'

    def __init__(self, expression, distinct=False, ordering=None, separator=',', **extra):
        super().__init__(
            expression,
            distinct='DISTINCT ' if distinct else '',
            ordering=' ORDER BY %s' % ordering if ordering is not None else '',
            separator=' SEPARATOR "%s"' % separator,
            output_field=CharField(),
            **extra
        )

Model1.objects.values(
    "field1",
    comma_sep_field=Model2.objects.values("field12").filter(
        field12=OuterRef("field12")
    ).values(value=GroupConcat("commaSeparatedField"))
)
4 Likes
4

Thanks for the response - I think I should mention the actual use case. There is a table with each row containing many private and public IP addresses - and I want to display the rows with each row showing Public and Private IP addresses concatenated with commas as a single string for private IP address and as a single string for public IP address. These IP addresses are stored in 2 separate tables.

5

thank you @charettes seems to work well!

can be improved slightly by adding:

class GroupConcat(Aggregate):
    function = 'GROUP_CONCAT'
    template = '%(function)s(%(distinct)s%(expressions)s%(ordering)s%(separator)s)'
    allow_distinct = True
1 Like
6

For anyone interested, there is ongoing work to add support for a core StringAgg aggregate that would use GROUP_CONCAT on MySQL. If everything goes according to plan it should be part of 5.2 with support for order_by and distinct on backends that support it.

3 Likes