I use SQLite and have a django model with the below data:

I would like to use an aggregate my table so that I can get my data in the below format:

How do I do this with Django.

I'm thinking this may be the answer but Django doesn't seem to have ArrayAgg. This looks to be a PostgreSQL function

Test_Model.objects.annotate(dname=ArrayAgg('name')).values()

Do you know how I can achieve this without using PostgreSQL? Thank you!

CodePudding user response：

For PostgreSQL, Django has an ArrayAgg function [Django-doc]:

from django.contrib.postgres.aggregates import ArrayAgg

Test_Model.objects.values('name').annotate(dname=ArrayAgg('value')).order_by('name')

CodePudding user response：

You can use groupby from itertools , then create the aggregate for any databases:

>>> from itertools import groupby
>>> [{'Name': key, 'Value': list(item.Value for item in grp)} for key, grp in
    groupby(Test_Model.objects.order_by('Name'), key=lambda x: x.Name)] 

[{'Name': 'A', 'Value': [1]},
 {'Name': 'B', 'Value': [2, 4]},
 {'Name': 'C', 'Value': [7, 5]}]

CodePudding user response：

First create your own aggregate like this

from django.db.models import Aggregate

class GroupConcat(Aggregate):
    function = 'GROUP_CONCAT'
    template = '%(function)s(%(distinct)s%(expressions)s)'

    def __init__(self, expression, distinct=False, **extra):
        super(GroupConcat, self).__init__(
            expression,
            distinct='DISTINCT ' if distinct else '',
            output_field=CharField(),
            **extra)

After creating this use below query

Test_Model.objects.values('name').annotate(dname=GroupConcat('value')).order_by('name')