Django ORM: Text aggregator on subquery

Question

I banged my head on this one:

I have 2 models, and I am trying to design a custom manager/queryset which will allow me to annotate to each Series the id of the linked puzzles satisfying certain conditions in the format '2,13,26'.

The simplified models:

class Series(models.Model):
    puzzles = models.ManyToManyField(
        Puzzle, through='SeriesElement', related_name='series')
    is_public = models.BooleanField(null=False, blank=False, default=False)

class Puzzle(models.Model):
    pass

my custom aggregator:

from django.db.models.aggregates import Aggregate
from django.db.models.functions import Coalesce
from django.db.models.fields import CharField
from django.db.models.expressions import Value

class GroupConcat(Aggregate):
    """
    according to https://stackoverflow.com/questions/10340684/group-concat-equivalent-in-django
    according to https://stackoverflow.com/a/55216659
    would be compatible with MySQL and SQLite
    """
    function = 'GROUP_CONCAT'

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


    def as_sqlite(self, compiler, connection, **extra):
        return super().as_sql(compiler,
                              connection, 
                              template='%(function)s(%(distinct)s%(expressions)s%(ordering)s)',
                              **extra)

one tentative to achieve my goal:

pzl_sub = apps.get_model('puzzles', 'Puzzle').objects.filter(series__id= OuterRef('id'))
pzl_sub = pzl_sub.filter(series_elements__isnull=False).add_nb_public_series().filter(nb_public_series=5)
pzl_ids= pzl_sub.order_by().values('id')
qs = Series.objects.annotate(id_str_pzl = GroupConcat(pzl_ids))

I obtain only one puzzle.id that fit the specified conditions, instead of the concat of all of the puzzle.ids that fit the conditions

Any clue on what I'm doing wrong?

Answer 1

After days of fighting, and testing everything, I finally figured it out:

pzl_sub = apps.get_model('puzzles', 'Puzzle').objects.filter(series__id= OuterRef('id'))
pzl_sub = pzl_sub.filter(series_elements__isnull=False).add_nb_public_series().filter(nb_public_series=5)
pzl_ids= pzl_sub.order_by().values('series__id')
qs = Series.objects.annotate(id_str_pzl = Subquery(pzl_ids.annotate(result=GroupConcat('id')).order_by().values('result')))

in pzl_ids, we need to single out values('series__id')

and only in a second step, we need to annotate with the aggregator, and single-out again the resulting value ...