having problem to use annotate in django to return nested list

Question

I have a model in django named Courses and it has a ManyToManyField which is related to the teachers Model. now in the views file I'm gonna make an APIView to return a list of courses which each courses has a field that returns teachers names in String which is separated by comma. I wanna use annotate and I used Concat to return each courses and in this Concat I used an ArrayAgg to return a list of teachers name.

it raises this error:

django.core.exceptions.FieldError: Cannot compute None('Concat(ConcatPair(Value('{"uuid": '), ConcatPair(Value('"'), ConcatPair(Cast(F(courses__pk)), ConcatPair(Value('",'), ConcatPair(Value('"title":'), ConcatPair(Value('"'), ConcatPair(Cast(F(courses__course_title)), ConcatPair(Value('",'), ConcatPair(Value('"courseType":'), ConcatPair(Value('"'), ConcatPair(Cast(F(courses__course_category__category_name)), ConcatPair(Value('",'), ConcatPair(Value('"rate":'), ConcatPair(Cast(F(courses__course_rate)), ConcatPair(Value(','), ConcatPair(Value('"price":'), ConcatPair(Cast(F(courses__course_price)), ConcatPair(Value(','), ConcatPair(Value('"discount":'), ConcatPair(Cast(F(courses__course_discount)), ConcatPair(Value(','), ConcatPair(Value('"isWishedList":'), ConcatPair(Value('"'), ConcatPair(Value('",'), ConcatPair(Value('"imageUri":'), ConcatPair(Value('"'), ConcatPair(Cast(F(courses__course_main_image)), ConcatPair(Value('",'), ConcatPair(Value('"teachers":'), ConcatPair(StringAgg(F(courses__course_teacher__name), Value(', '), distinct=True), ConcatPair(Value(','), ConcatPair(Value('"institution":'), ConcatPair(Value('"'), ConcatPair(Cast(F(courses__course_institution__name)), Value('"}'))))))))))))))))))))))))))))))))))))'): 'Concat(ConcatPair(Value('{"uuid": '), ConcatPair(Value('"'), ConcatPair(Cast(F(courses__pk)), ConcatPair(Value('",'), ConcatPair(Value('"title":'), ConcatPair(Value('"'), ConcatPair(Cast(F(courses__course_title)), ConcatPair(Value('",'), ConcatPair(Value('"courseType":'), ConcatPair(Value('"'), ConcatPair(Cast(F(courses__course_category__category_name)), ConcatPair(Value('",'), ConcatPair(Value('"rate":'), ConcatPair(Cast(F(courses__course_rate)), ConcatPair(Value(','), ConcatPair(Value('"price":'), ConcatPair(Cast(F(courses__course_price)), ConcatPair(Value(','), ConcatPair(Value('"discount":'), ConcatPair(Cast(F(courses__course_discount)), ConcatPair(Value(','), ConcatPair(Value('"isWishedList":'), ConcatPair(Value('"'), ConcatPair(Value('",'), ConcatPair(Value('"imageUri":'), ConcatPair(Value('"'), ConcatPair(Cast(F(courses__course_main_image)), ConcatPair(Value('",'), ConcatPair(Value('"teachers":'), ConcatPair(StringAgg(F(courses__course_teacher__name), Value(', '), distinct=True), ConcatPair(Value(','), ConcatPair(Value('"institution":'), ConcatPair(Value('"'), ConcatPair(Cast(F(courses__course_institution__name)), Value('"}'))))))))))))))))))))))))))))))))))))' is an aggregate

I changed th code and replaced ArrayAgg with Subquery, it works good but I want a list of teachers name but it returns a dict like "{"value1", "value2"}" instead. this is my code with Subquery:

Subquery(ArchSchoolCourse.objects.prefetch_related("course_teacher").filter(
                        pk=OuterRef('courses__pk')
                    ).annotate(
                        course_teachers=GroupConcat("course_teacher__name", distinct=True, separator=",") if settings.DB_ENGINE == "django.db.backends.mysql"
                else ArrayAgg("course_teacher__name", distinct=True) if settings.DB_ENGINE == "django.db.backends.postgresql" else None,
                    ).values_list("course_teachers", flat=True).all()),

score 0 · Answer 1 · answered May 22 '23 at 10:44

let's suppose your model looks like this

class Teachers(models.Model):
      name = models.CharField(blank=False, null=False, max_length=255)
    

class Courses(models.Model):
      name = models.CharField(blank=False, null=False, max_length=255)
      teachers = models.ManyToManyField(to=Teachers)

what you are trying to do can be achieved by using the serializers

from rest_framework import serializer

class TeacherSerializer(serializers.ModelSerializer):
    class Meta:
         model = Teacher
         fields = ["name"]
        

class CourseSerializer(serializers.ModelSerializer):
    teachers = serializers.SlugRelatedField(many=True, slug_field='name',allow_null=True)

    class Meta:
        fields = '__all__'
        model = Courses

Now you can access list of courses with each course having list of teacher

courses = Courses.objects.all()
serialized_courses = CourseSerializer(courses, many=True)
serialized_courses.data

having problem to use annotate in django to return nested list

1 Answers1