Django - How to use Subquery and count

Question

I'm trying to achive an annotated query and here's my code.

# models.py

STATUS_CHOICES = (
    ("submitted", "제출됨"),
    ("in-review", "검토중"),
    ("rejected", "반려됨"),
    ("approved", "승인됨"),
)

class ClassReview(models.Model):
    class = models.ForeignKey("class.Class", on_delete=models.PROTECT)
    status = models.CharField(max_length=10, choices=STATUS_CODE, default="submitted")

class ClassReviewVote(models.Model):
    target = models.ForeignKey("class.ClassReview", on_delete=models.PROTECT)
    vote = models.BooleanField(null=True)

# selectors.py

def get_classreview_set(class_id):
    review_set = ClassReview.objects.filter(class_id=class_id, status="approved")
    review_Set = review_set.annotate(
        vote_up_count=Subquery(
            ClassReviewVote.objects.filter(target_id=OuterRef("pk"), vote=True)
            .values("target")
            .annotate(count=Count("target"))
            .values("count"),
            output_field=IntegerField(),
        )
    )
    return review_set

# serializers.py

def classreview_serializer(classreview):
    data = dict()

    ...
    data["vote_up_count"] = classreview.vote_up_count
    ...

    return data

# views.py

class ClassDetailView(TemplateView):
    ...
    def get(self, request, *args, **kwargs):
        class_id = request.kwargs.get("class_id")

        review_set = get_classreview_set(class_id)

        context = {
            review_list = classreview_serializer(review_set)
        }
        ...
    ...

I want to annotate vote up count of each review. But it keeps raising Error "more than one row returned by a subquery used as an expression". When logged in user and someone else voted up. What is happening?

user_12427896 · Answer

I found why error occurred. There is ordering in model's META class. So it didn't work well. I guess error occurred when GROUP_BY happens, cuz there is ordering already.

Here's code:

# selectors.py

def get_classreview_set(class_id):
    review_set = ClassReview.objects.filter(class_id=class_id, status="approved")
    review_Set = review_set.annotate(
        vote_up_count=Subquery(
            ClassReviewVote.objects.filter(target_id=OuterRef("pk"), vote=True)
            .order_by("target")   # <------ NEW LINE
            .values("target")
            .annotate(count=Count("target"))
            .values("count"),
            output_field=IntegerField(),
        )
    )
    return review_set

It works!! I don't know what's the problem exactly until now. But I fixed it. If there is someone knows what's happening exactly, let me know plz.

Django - How to use Subquery and count

1 Answers