0

I have 3 models

class QuestionsModel(models.Model):
    created = models.DateTimeField(auto_now_add=True)
    Question = models.CharField(max_length=200)


class AnswersModel(models.Model):
    Question = models.ForeignKey(QuestionsModel, related_name='QuestionAnswer')
    Answer = models.CharField(max_length=200)


class UsersAnswerModel(models.Model):
    Answer = models.ForeignKey(AnswersModel, related_name='UsersAnswer')
    RegistrationID = models.CharField(max_length=200)

I am trying to Count How many UsersAnswer the Question

what I tried :

class DashboardAdmin(admin.ModelAdmin):
    class Meta:
        model = QuestionsModel
    change_list_template = 'admin/Dashboard_change_list.html'
    date_hierarchy = 'created'

    def has_add_permission(self, request):
        return False

    def changelist_view(self, request, extra_context=None):
        response = super().changelist_view(
            request,
            extra_context=extra_context,
        )
        try:
            qs = response.context_data['cl'].queryset
        except (AttributeError, KeyError):
            return response

        metrics = {
            'totalAnswers' : models.Count('QuestionAnswer'),
            'totalUsersAnswer' : models.Count(UsersAnswer=OuterRef('QuestionAnswer'))
        }

        response.context_data['summary'] = list(
            qs
            .values('Question')
            .annotate(**metrics)
            .order_by('-totalUsersAnswer')
        )

        return response

admin.site.register(DashboardModel, DashboardAdmin)

I could not solve

'totalUsersAnswer' : models.Count(UsersAnswer=OuterRef('QuestionAnswer'))

how to count nested foreign key

any help please

Bosoud
  • 158
  • 4
  • 24

2 Answers2

0
class AnswersModel(models.Model):
    Question = models.ForeignKey(QuestionsModel, related_name='QuestionAnswer')
    Answer = models.CharField(max_length=200)


class UsersAnswerModel(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL,on_delete=models.PROTECT)
    Answer = models.ForeignKey(AnswersModel, related_name='UsersAnswer')
    RegistrationID = models.CharField(max_length=200)

Q- How many users answer a Question? e.g

 user_answer = UsersAnswerModel.objects.filter(Answer__question__id=2).count()
 You can now annotate based on your need.

Your models need to be renamed

  1. AnswersModel to simply Answer

  2. UsersAnswerModel to UserAnswer and there should be a user entity as a field as I have done above.

3.Fieldnames in model is better lowercased.

SDRJ
  • 532
  • 3
  • 11
  • I got this error "Related Field got invalid lookup: question" – Bosoud Feb 17 '21 at 12:14
  • @BoSoud-Can you share the query you are doing ? – SDRJ Feb 17 '21 at 13:15
  • I just modify the this lines ``` user_answer = UsersAnswerModel.objects.filter(Answer__question__id=20).count() metrics = { 'totalAnswers' : models.Count('QuestionAnswer'), 'totalUsersAnswer' : user_answer } response.context_data['summary'] = list( qs .values('Question') .annotate(**metrics) .order_by('-totalUsersAnswer') ) return response ``` – Bosoud Feb 18 '21 at 10:57
0

I have Solve it by add QuestionAnswer__UsersAnswer

metrics = {
        'totalAnswers' : models.Count('QuestionAnswer', distinct=True),
        'totalUsersAnswer' : models.Count('QuestionAnswer__UsersAnswer'),
    }
Bosoud
  • 158
  • 4
  • 24