Count how many occurrence of a value in table

Ive looked a formsets before, hopefully all makes sense.

I’ve created the models, but i have noticed that the reposnse model allows for the same project to answer the same question multiple times. This i think is a problem because of the scoring.

I realise i am way out of my depth on this Ken, does this setup allow for me to setup a way to say only question1 can be answered once by a project.

Yes. You’ll want a database constraint on the Response model to ensure the combination (project_name, questionnaire) is unique, along with a unique constraint in Answer on (response, question). (I just noticed that you don’t have a response field in the Answer model you posted.) From there, your views and formsets will take care of the rest.

Like this

class Questionnaire(models.Model):
    title = models.CharField(max_length=50, blank=False, unique=True)

class Question(models.Model):
    questionnaire = models.ForeignKey(Questionnaire, on_delete=models.CASCADE)
    sequence = models.IntegerField(blank=True, null=True)
    question = models.TextField(blank=True)

class Response(models.Model):
    project_name = models.ForeignKey(Project, on_delete=models.CASCADE,unique=True)
    questionnaire = models.ForeignKey(Questionnaire, on_delete=models.CASCADE, unique=True)
    user = models.CharField(max_length=50, blank=True) 

class Answer(models.Model):
    RAG_Choices = [
        ('Green', 'Green'),
        ('Amber', 'Amber'),
        ('Red', 'Red')
    ]
    question = models.ForeignKey(Question, on_delete=models.CASCADE, unique=True)
    answer = models.CharField(max_length=50, blank=True, choices=RAG_Choices)
    response = models.ForeignKey(Response, on_delete=models.CASCADE, unique=True)

Not quite - it’s the combination of project and questionnaire in Response that needs to be unique along with the combination of question and response being unique in Answer. Those individual fields need to each appear muliple times.

See Constraints reference | Django documentation | Django (and then for background info see Model Meta options | Django documentation | Django)

Like this Ken? :confused:

class Questionnaire(models.Model):
    title = models.CharField(max_length=50, blank=False, unique=True)

class Question(models.Model):
    questionnaire = models.ForeignKey(Questionnaire, on_delete=models.CASCADE)
    sequence = models.IntegerField(blank=True, null=True)
    question = models.TextField(blank=True)

class Response(models.Model):
    project_name = models.ForeignKey(Project, on_delete=models.CASCADE,unique=True)
    questionnaire = models.ForeignKey(Questionnaire, on_delete=models.CASCADE, unique=True)
    user = models.CharField(max_length=50, blank=True)

    class Meta:
        constraints = [
                models.UniqueConstraint(fields= ['project','questionnaire']),
            ]
 

class Answer(models.Model):
    RAG_Choices = [
        ('Green', 'Green'),
        ('Amber', 'Amber'),
        ('Red', 'Red')
    ]
    question = models.ForeignKey(Question, on_delete=models.CASCADE, unique=True)
    answer = models.CharField(max_length=50, blank=True, choices=RAG_Choices)
    response = models.ForeignKey(Response, on_delete=models.CASCADE, unique=True)
    
    class Meta:
        constraints = [
                models.UniqueConstraint(fields= ['question','response']),
            ]

Yes on the constraints.

But, you still have unique=True on individual fields that can’t be unique. (Response.project_name, Response.questionnaire, Answer.question, Answer.response)

Also, a couple other things you might want to consider.

  • Question.sequence is an IntegerField - it doesn’t make sense to allow blank=True and null=True. (Null=True is ok if you don’t care what order the questions are presented, so it’s technically not “wrong”.)

  • Question.question - it doesn’t seem to make much sense to allow blank=True. As a UI/UX issue, at a minimum I would suggest using something like “(Question not available)” as a default value.

Hi Ken,

I have updated the models, removed the unique fields, and add the constrains. So i think thats it for the models. But after adding a questionnaire, question, response, answers. How do i link all of this together.

I’ve created a questionaire (title) added the questions, linked to the questionnaire. But i need to create a respone before answering a question - how do i do that all within a single form?

So the form will need to create the respone entires prior to answering the question?

How will this be handled in the formset, as i want to be able to choose which questionniare to answer. It might be that i only answer 1 of the 8 initially.

Think im getting there, but sorry for more questions.

If you dont mind answering these for me.

On the project_details page i will have a button/link to take me to the questionnaire i want to complete.

the view for this link will need? to pass in the questionaire id as well as the project_id

This will then load the questionaire page? But how do i set the formset to only load the questions relating to that questionaire?

this was my test view to see if i could get the form to load:

def formset_view(request):
    context ={}
    # creating a formset
    fundamentals_questionnaire = modelformset_factory(Answer, fields=['answer'])
    formset = AnswersForm()
    # Add the formset to context dictionary
    context['formset']= formset
    return render(request, "pages/formset.html", context)

This is my view, url and link when trying to do it properly (doesn’t work):

<a class="dropdown-item" href="{% url 'questions' project.id questionnaire_id %}?next={{ request.path|urlencode }}">formset</a>

 path("questions/<int:project_id>/<int:questionnaire_id>", view=add_fundamental_answers_view, name="questions"),

@login_required
def add_fundamental_answers_view(request,project_id,questionnaire_id):
    project = get_object_or_404(Project, pk=project_id)
    questionnaire = get_object_or_404(Questionnaire, pk=questionnaire_id)
    next = request.POST.get('next', '/')
    if request.method =='POST':
            formset = AnswersForm()(request.POST)
            if formset.is_valid():
                answer = formset.save(commit=False)
                answer.project_name = project
                answer.save()
                return HttpResponseRedirect(next)
    else:
        form = AnswersForm()
    return render(request, 'pages/formset.html', {'project': project, "form": form})   

using the test view i can load the html. Within the html template i am rendering the form with {{ formset }} which is displaying the answer field from the Answer model.

How do i render the question?

At the moment within my html page, i can select the answer to the question, but this doesn’t update the database, but i think cause its not aware of the project_id or questionnaire_id or even what question the answer relates to?

Apologies for the delay. It’s been an, errr, interesting 36 hours for me

Correct, those would be the two parameters you need.

The questions relating to a questionnaire is Question.objects.filter(questionnaire_id=<whatever you named the questionnaire id being passed in as a parameter>)

So there actually are a couple of ways of handling this - each with their own implications with how you manage the models.

Your choices include:

  • Use a regular formset, not a model form set. Pass the list of questions and current answers (if any) as initial data to the formset.

  • Use a model formset on Answer. Iterate over the individual forms and render the text of the question by accessing it through the form.instance attribute.

No worries Ken, you dont need to apologise. I appreciate all your help you’ve given me.

This is the view i am building out which is being called from the project page which is just a landing page for the project. I am passing in the questionnaire_id as part of the of this view.

@login_required
def add_fundamental_answers_view(request, project_id, questionnaire_id):
    project = get_object_or_404(Project, pk=project_id)
    questionnaire = Question.objects.filter(questionnaire_id=questionnaire_id)
    next = request.POST.get('next', '/')
    if request.method =='POST':
            formset = AnswersForm()(request.POST)
            if formset.is_valid():
                answer = formset.save(commit=False)
                answer.project_name = project
                answer.save()
                return HttpResponseRedirect(next)
    else:
        form = AnswersForm()
    return render(request, 'pages/formset.html', {'project': project, "form": form,'questionnaire':questionnaire})   

to access this view and to pass in the questionnaire_id i have created a for loop for the questionnaires within the project details page

{% for item in questionnaire %} <a class="dropdown-item" href="{% url 'questions' project.id questionnaire.id %}?next={{ request.path|urlencode }}">Fundamentals</a> {% endfor %}

The url is:
path("questions/<int:project_id>/<int:questionnaire_id>", view=add_fundamental_answers_view, name="questions"),

But im getting a error when rendering the project details page because of this link
"{% url 'questions' project.id questionnaire.id %}?next={{ request.path|urlencode }}">Fundamentals

NoReverseMatch at /show_project_details/1
Reverse for 'questions' with arguments '(1, '')' not found. 1 pattern(s) tried: ['questions/(?P<project_id>[0-9]+)/(?P<questionnaire_id>[0-9]+)$']

I think this is becuase the questionnaire_id is missing, but im not sure why its missing, becuase i think it should be generated from for for loop

Where am i going wrong, Ken?

Tom.

I’ve fixed my issue on the link - i should have been passing in item_id rather than questionnaire_id

So its just rendering the form now.

What would you do - Formset or ModelFormset ?

Also when i submit answer the the rendered question - i get the following error
'AnswersForm' object is not callable

View

def add_fundamental_answers_view(request, project_id, questionnaire_id):
    project = get_object_or_404(Project, pk=project_id)
    questionnaire = Question.objects.filter(questionnaire_id=questionnaire_id)
    next = request.POST.get('next', '/')
    if request.method =='POST':
            formset = AnswersForm()(request.POST)
            if formset.is_valid():
                answer = formset.save(commit=False)
                answer.project_name = project
                answer.save()
                return HttpResponseRedirect(next)
    else:
        formset = AnswersForm()
    return render(request, 'pages/formset.html', {'project': project, "formset": formset,'questionnaire':questionnaire})   

Do I want to make sure that the instances of the Answer models are created before, or do I want to create them after the questionnaire is asked?

If before, then I’m likely to want to use the ModelFormset. If I’d prefer after, then I’d go with the regular formset. It might also depend upon whether I want to present all the questions on one page, or do it with each question (or some subset of questions) on multiple pages.
(In reality, it’s likely to only make a difference in the situation where the questionnaire isn’t completed - what the scoring is going to look like if an individual has only answered 10 of the 20 questions.)

You’ve got an extra set of parens there:
formset = AnswersForm(request.POST)

I missed that. Thanks, Ken.

Im using model forms at the moment, so i’d probably stick with that.

I’d want the scoring to be based upon what has been answered. So if 10 are answered then the result will just show the number of greens, reds, ambers within that 10. But this should be able to go back and update the answers which should recalculate. I going to present to scores on the project page.

Im struggling to render the form, all i get is 1 answer selection box.

                                       {{ formset.instance}}
                                       <label for="test"></label>
                                       {{ formset }}

How do i present the question and then the answer selection box? I assuming that i need to {% for %} but what do i need to loop through?

would it be questionnaire.questions and answer.answers?

I’ll need to see your current view, along with the form and formset definitions. (I know you’ve got some snippets above, but I also know most of that might be considered a “work in progress”.)

This is my current view, it is work in progress.

def add_fundamental_answers_view(request, project_id, questionnaire_id):
    project = get_object_or_404(Project, pk=project_id)
    questionnaire = Question.objects.filter(questionnaire_id=questionnaire_id)
    next = request.POST.get('next', '/')
    if request.method =='POST':
            formset = AnswersForm(request.POST)
            if formset.is_valid():
                answer = formset.save(commit=False)
                answer.project_name = project
                answer.questionnaire = questionnaire 
                answer.save()
                return HttpResponseRedirect(next)
    else:
        formset = AnswersForm()
    return render(request, 'pages/formset.html', {'project': project, "formset": formset,'questionnaire':questionnaire})   

My form is as basic as it can be for now:

class AnswersForm(ModelForm):
    class Meta:
        model = Answer
        fields = ('answer',)

        widgets = {
            'answer': forms.Select(attrs={'class': 'form-select'}),
        }

Formset Definitions?

Yes, your use of (either) formset_factory or modelformset_factory to define the formset (modelformset) class. See Using a model fromset in a view.

In what you have here, you’re creating a singular form, not a formset (or modelformset).

Would this be just adding the modelformset_factory(Question, fields=('question'))

In the example they include request.FILES but im not sure if i need that?

def add_fundamental_answers_view(request, project_id, questionnaire_id):
    project = get_object_or_404(Project, pk=project_id)
    questionnaire = Question.objects.filter(questionnaire_id=questionnaire_id)
    next = request.POST.get('next', '/')
    formset = modelformset_factory(Question, fields=('question'))
    if request.method =='POST':
            if formset.is_valid():
                answer = formset.save(commit=False)
                answer.project_name = project
                answer.questionnaire = questionnaire 
                answer.save()
                return HttpResponseRedirect(next)
    else:
        formset = AnswersForm()
    return render(request, 'pages/formset.html', {'project': project, "formset": formset,'questionnaire':questionnaire})   

I get this error when rendering the page

QuestionForm.Meta.fields cannot be a string. Did you mean to type: ('question',)?

A more detailed response will come later today - I promise.

The quick response is that creating and using a formset consists of two steps. (See Formsets | Django documentation | Django)

First, you use one of the _factory functions to create your formset class. While not truly accurate, you can think of formset_factory as being analogous to the class definition class SomeForm(form.Form). The factory is not creating a formset. It’s creating a Class that can be used to create a formset.
The second step is to create the formset. That’s done by creating an instance of the class returned by the factory.

The examples on the Formsets page do show this.

Premise, assumptions, and caveats:

  • I’m only showing the GET part of the view. This should give you enough to move forward with the POST
  • Two parameters are supplied via url variables, project_id and questionnaire_id.
  • We’re going to create all necessary instances of Answer before building the formset.
  • We’re going to create a ModelForm on Answer, using the Question as a label.
  • This is actually easier to do in Django 4.0 due to recent changes. However, I’m not familiar enough with those changes yet to present them as part of this solution.
  • There may actually be a couple of easier ways to do some of this. I tried to present as explicit a formulation as possible to help you see all the steps involved without any significant shortcuts.

The template fragment for rendering the formset looks like this:
formset.html

<form method="post">
    {{ formset.management_form }}
    {% for form in formset %}
        {{ form.id }} {{ form.instance.question.question }} {{ form.answer }}<br>
    {% endfor %}
</form>

We’re rendering the management form, the id and answer fields from the form, and following the relationship through to the question to get the text of the question.

The view then looks like this:

def get_questionnaire(request, project_id, questionnaire_id):

    # Get the Response object for the parameters
    response = Response.objects.get(
        project_id=project_id, questionnaire_id=questionnaire_id
    )

    AnswerFormSet = modelformset_factory(Answer, fields=('answer',), extra=0)

    answer_queryset = Answer.objects.filter(response=response
    ).order_by('question__sequence'
    ).select_related('question')

    if request.method == 'POST':
        # Left as an exercise for the reader
        pass
    else:
        # Get the list of questions for which no Answer exists
        new_answers = Question.objects.filter(
            questionnaire__response=response
        ).exclude(
            answer__response=response
        )

        # This is safe to execute every time. If all answers exist, nothing happens
        for new_answer in new_answers:
            Answer(question=new_answer, response=response).save()

        answer_formset = AnswerFormSet(queryset=answer_queryset)

    return render(request, 'formset.html', {'formset': answer_formset})

Blimey. Thank, Ken.

That woiuld have taken me a while to work out.

I feel bad asking this, but i get any error, not sure where this is coming from?
django.core.exceptions.FieldError: Cannot resolve keyword 'project_id' into field. Choices are: answer, id, project_name, project_name_id, questionnaire, questionnaire_id, user

I assumimg its this line

    response = Response.objects.get(
        project_id=project_id, questionnaire_id=questionnaire_id
    )

but not sure why, cause project ID is being passed in on the request.

I’m calling the view using

{% for item in questionnaire %}
 <a class="dropdown-item" href="{% url 'questions' project.id item.id %}?next={{ request.path|urlencode }}">{{ item.title }}</a>
{% endfor %}