Count how many occurrence of a value in table

Within my app, I have some forms that allow the user to select from a dropdown and I’m trying to count how many times RED , GREEN and AMBER have been selected across 20 different fields.

I think i need to use something like result = Fundamentals.objects.annotate(Count = Count('Green')) But this seems to require the field name to be supplied.

Is there a way to say all fields ?

No, there’s no facility for doing that.

Side note: A table structure with 20 columns all containing the same data selections and needing to be queried across columns is a ripe candidate for refactoring and normalization.

The table contains 20 fields, but each field is a question and answer with the options being Red, Green or Amber. The table only holds data relevant to the subject of the questionnaire. That being fundamentals of the project.

I’m equally rubbish at both django and databases so what would normalisation do?

Other than normalisation, which I will look into.

The other option would be to query each field and somehow create some kind of append to a variable?

The general pattern for something like this would be:

model Questionnaire
  # Some field to identify a name for the questionnaire, e.g "title"

model Question
  questionnaire: FK to Questionnaire
  sequence: Used to order the questions being presented
  question: Text of the question

model Response
  respondent: FK to User
  questionnaire: FK to Questionnaire

model Answer
  response: FK to Response
  question: FK to Question
  answer: Answer to the question

The purpose of going in this direction is the flexibility it provides in the construction of the Questionnaires, along with facilitating different ways to track and score answers both within and between questions and repondants. It also makes it easier to build your templates for presenting the questions.

If this is the best thing to do, I can change my models. There are 8 questionnaires all with roughly 20 questions so I’d be better creating 2 models for the questions ?

Or would i create a Question and Answer model for each questionnaire. something like FundamentalQuestion, FundamentalAnswers

Before I redo all models/forms/templates what would be the query I’d need to write to tell me how many green answers have been provided for answers within a questionnaire.

I mean as a simple example? Cause even if I separate out, isn’t the problem still the same I.e return all the green answers within.a questionnaire which at the moment is a single model?

Yes. Among other things, this structure does not limit you to 20 questions, nor does it assume that every questionnaire have 20 questions.

Side note: I don’t think it matters too much for this example, but this structure is created under the assumption that multiple people would be answering the same questionnaire.

Given the structure above, and a particular Questionnaire ID named “q”, the number of all “Green” answers across all responses (from all users) across all questions would be something like:

number_green = Answer.objects.filter(
    answer = "Green",
    question__questionnaire_id = q
).count()

Your choice of filters would let you break this down by any combination of person, question, response, etc. Also, if you wanted to get counts for each of Red, Amber, Green, you could use aggregation to do that.

Amazing thanks Ken. I get started :slight_smile:

Sorry Ken, just to be clear. Should i be creating a 2 models for each questionnaire (Questions and Answers) or 1 big model for questions and 1 big for answers?

One model for all questionnaires, one model for all questions and one model for all answers.

1 Like

To kind of break down what Ken is saying -

He is basically saying to have a table that will hold all questionnaires. This table is kind of the master table in a sense. It has every single questionnaire and its associated id. That way you can have as many questionnaires as you like.

Then you have a question table. This table can have multiple rows that ties back to a single Questionnaire. You would have a row for every question related to a Questionnaire. And they tie together using sequence. So you ultimately have a one-to-many. Questionnaire being the one and Question being the many.

Which is definitely the right approach.

I would maybe change Response though. Instead of an FK to Questionnaire, I’d have an FK to Answer. Because that way you tie a respondent to their answer. Then Answer ties back to Question already which ties back to Questionnaire.

Ken is basically taking the approach of having a Questionnaire and then tying it to all of its needed piece, but breaking it / isolating data where it makes the most sense.

That might be a misinterpretation of what a “Response” is. A Response is one persons’ set of answers for a Questionnaire. In other words, if you’re answering the Questionnaire, you’re answering 20 questions. (If you may need to answer this same questionnaire multiple times, the Response would include a date field, allowing it to identify which Response an answer is associated with. Your answers may change between December 15th and January 15th.)

So, in natural language terms, an Answer is to a Response like a Question is to a Questionnaire. The Questionnaire contains the set of questions associated with it. A Response contains the set of Answers an individual supplies for each Question.

Now, that’s not to say that other approaches aren’t valid - there are a number of such valid approaches. However, this is the design intent of this approach.

In your example - response has two pieces of data - a user and the id of a questionnaire. That’s not going to give any value / worth.

So, in natural language terms, an Answer is to a Response like a Question is to a Questionnaire. The Questionnaire contains the set of questions associated with it. A Response contains the set of Answers an individual supplies for each Question.

In your example - A response does not contain a set of Answers an individual supplied. It contains a user and a questionnaire id. To make a response tie back to an answer - you would want to tie Response to Answer and Answer to Question and Question to Questionnaire. That would allow you to achieve the overall goal of having multiple questionnaires, each having multiple questions, and tying a user back to their answers.

You’re still not seeing it.

The set of all answers for a response is Response.answer_set.all(). You follow the reverse link of the ForeignKey relationship. That’s the purpose of the Response FK within an Answer.

Try looking at it another way.

I hand you a physical questionnaire on paper.
That paper may have two parts:

  • Part 1: Your name and the title of the questionnaire you are answering. (That’s the “Response” itself)
  • Part 2: A list of questions with spaces for answers.
    • The questions are the set of questions contained by the Questionnaire
    • The answers you supply are what goes in the Answer table

I like the idea of knowing the user who completed the questionnaire. But it wasn’t something i was thinking about.

The questionnaires are basically a set of question to help review a project. Each set of questions is based on a particular area of the project being reviewed. i.e the packaging.

The project has its own model - where the person reviewing would create the enrty (project_name etc) and then start the review process.

With this in mind what would be the correct FK. would l have a FK from answer to project?

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

Not directly from Answer, no. (At least not in the “common case”)

There are a number of questions about the specific needs for these questionnaires that could change the base recommendation.

For example:

  • You mention that “Each set of questions is based on a particular area of the project being reviewed.” Does this mean that you might have multiple Questionnaires for a project? (This solution provides the ability to handle that.)
  • Is there ever any need to answer the same questionnaire for a project a second or third time? (This solution provides the ability to handle that.)
    • If so, is there value in tracking the first response in addition to the later responses?

In the “common case” solution, an Answer is already related to a Question, which has a relationship to a Questionnaire, which can then be related to a Project. (You would likely want the Questionnaire to have an FK to Project, allowing you to have multiple Questionnaires for each Project.)

As I tried to explain above, think of the questionnaires as if you were working with physical paper.

You might want to have a cover sheet - the “Response”.

You may have the “Questions” identified in a book somewhere.

You might then provide answers to those questions, the “Answers”, by identifying the question being addressed by that particular answer. (There’s not necessarily a need to copy the text of the question to the answer - think of something like an SAT where the answer sheets are independent of the questions in the book.)

The “Questionnaire” is the identification of the set of questions to answer.

Someone then answers all the questions, puts them in a folder, and places that folder in the filing cabinet for the “Project”, perhaps in a drawer labeled “Responses to Questionnaire X”.

Those items attached together or otherwise marked are your general indications where the FKs reside.

A person (“User”) may sign the cover sheet identifying themselves as the person filling it out, so you need an FK from “Response” to “User”.

The completed packet isn’t separated by question to be filed by question in the drawer. The answers are all attached to the single coversheet. This identifies the need for an FK from “Answer” to “Response”.

The completed Response packet is then filed as a unit in the cabinet, so your relationship is from the Response to the Questionnaire.

Now, where this “common case” solution doesn’t work are those cases where you wish to apply the same questionnaire to multiple projects, or to ask the same question in multiple questionnaires. That might introduce some ManyToMany relationships depending upon how the responses need to be analyzed. But all that gets really deep in to an analysis of the business model and what the ultimate objectives and desired results are.

Yes there are 8 questionnairs for a project.

I need to be able to update the answer later on, but i dont need to track the previous value.

The same questionnaire will be applied to many projects, and this is where the scoring comes in. As i want to know which project scored what for each questionnaire. The total Green,Red and Amber answers acorss all questionnaires will provide a overall score for which i can then identify the best project.

A question will never appear twice, each question is unique to a questionnaire

This mean i need to make a change to the FK to a ManyToMany?

Right i see the problem now. So if i use FK it a question within a questionnaire can only be linked to 1 questionnaire which can only be linked to 1 project.

If i use ManyToMany the question looks like its the same answers for all the projects linked to the questionnaire.

So do i need a intermediate model called ProjectQuestionnaires and link this to both Project and Questionnnaire using project_name and questionnaire_title

If you think about it, The “intermediate model” is a ManyToMany Through table. (Foreign keys to two other tables.)
As such, it is the effective representation of what I’ve previously described as the “Response” table. (It’s the Response table with an additional FK to Project.)
So yes, one effective solution would be to create this “ProjectQuestionnaire” table, which can contain any ancillary data needed (such as the reference to the User filling it out or a “last updated” date or anything else you wish to track). Then the Answer table would have it’s FK to ProjectQuestionnaire.
(Trying to avoid confusion - you can call it what you want. You can call it “Response”, you can call it “ProjectQuestionnaire”, you can pretty much call it anything - but you only need one of them. You won’t have both of them.)

I see what you are saying and all makes sense.

I will put this all together.

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)
    questionnaire = models.ForeignKey(Questionnaire, on_delete=models.CASCADE)
    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)
    answer = models.CharField(max_length=50, blank=True, choices=RAG_Choices)

Just 1 thing i would like to ask is around presenting the forms with questions and answers. Ive been working with just 1 model for the forms.

How do i use the forms with two models within a single view? You said earlier

How?

Thanks

First, I’m going to suggest not trying to use one of the Django generic CBVs. This is one of those cases where you’re probably better off working with an FBV.

There are two basic ways you can handle this - all questions on one page, or as a “wizard”, where the questionnaire is effectively broken down into multiple pages.

If you want all the questions on one page, my initial thoughts on this would be to create a formset for the answers, presenting the question as a label. That way, you’re creating one “thing” (the formset), but it contains however many forms are necessary for all the questions to be asked.

For creating wizards, I suggest taking a look at Django formtools. (We use it - it works great for us.)