Pivot Table for Wagtail Form Builder for Polling and Votes purpose

Question

I am trying to use Wagtail Form Builder for Voting and Polling purpose, and use HighCharts to display the results interactively on webpage.

the problems is that Wagtail FormSubmission class only stores information of each vote.

| vote user | question 1 | question 2 |
| jason     | A          |  C         |
| lily      |   D        |   B        |

But I want to get information like: How many users voted for A, B, C, D for Question 1, 2 respectively, and What are those users. Similar to do a Pivot Table for the FormSubmission results.

I understand I can do a QuerySet API Aggregation to get what I want, but I do not want to do this expensive manipulation every time when user visit the webpage.

I am thinking about using class-level attributes to achieve this.

Q: I am wondering what is the best practice to store those aggregation results in DB and update accordingly every time a Vote is submitted?

Answer 1

Wagtail form builder is not really suitable for this task. It's designed to allow non-programmers to construct forms for simple data collection - where it just needs to be stored and retrieved, with no further processing - without having to know how to define a Django model. To achieve this, all the data is stored in the FormSubmission model in a single field as JSON text, so that the same model can be re-used for any form. Since this isn't a format that the database engine understands natively, there's no way to perform complex queries on it efficiently - the only way is to unpack each submission individually and run calculations on it in Python code, which is going to be less efficient than any queryset functionality.

Instead, I would recommend writing a custom Django app for this. The tutorial in the Django documentation is a polls app, which should give you some idea of the way to go about it, but in short, you'll most likely need three models: a Question model containing the text of each question, an AnswerChoice model where each item is one of the possible answers for one question, and a Response model indicating which AnswerChoice a given user has chosen. With these models, you'll be able to perform queries such as "how many users answered A for question 1" with a queryset expression such as:

Response.objects.filter(question=1, answer_choice='A').count()