I am trying to display a summary of statuses by agent. Annotate looks like the way to go, but the data structure doesn't seem to allow me to loop through the objects and populate an html table cleanly. I've tried manipulating the result set further in my view before passing to the template, but I've mangled it so badly that I'm not sure if I'm approaching this correctly. Any feedback is appreciated.
I've attempted the query the database with a 'group by' query using the objects.values().annotate() methods. This outputs a list of dictionaries. If I could get the values as keys, that might work, but there is another list.
Querying the user model directly may inadvertently leave out any agents that don't have any QA events.
original views.py
def agent_summary(request):
lead_qas = LeadQA.objects.values('qa_agent', 'qa_status').annotate(Count('id'))
context = {'lead_qas': lead_qas}
return render(request, 'manager/agent_summary.html', context)
This gets me a data structure like:
{'qa_agent': 3, 'qa_status': 'Confirmed', 'id__count': 1}, {'qa_agent': 1, 'qa_status': 'Pending Review', 'id__count': 6}, {'qa_agent': 1, 'qa_status': 'Disqualified', 'id__count': 8}, {'qa_agent': 2, 'qa_status': 'Disqualified', 'id__count': 1}, {'qa_agent': 2, 'qa_status': 'Not Started', 'id__count': 4}, {'qa_agent': 1, 'qa_status': 'Not Started', 'id__count': 3}, {'qa_agent': 3, 'qa_status': 'Not Started', 'id__count': 4}, {'qa_agent': 1, 'qa_status': 'Confirmed', 'id__count': 4}
I can't quite pivot the keys into my html table.
modified views.py (worse, maybe?)
@staff_member_required
@login_required
def agent_summary(request):
lead_qas = LeadQA.objects.values('qa_agent', 'qa_status').annotate(Count('id'))
agents = User.objects.filter(is_staff=False)
qas = []
for agent in agents:
qas.append({'agent': agent.username, 'qa': list(LeadQA.objects.filter(qa_agent=agent).values('qa_status').annotate(Count('id')))})
context = {'lead_qas': lead_qas, 'agents': agents, 'qas': qas}
return render(request, 'manager/agent_summary.html', context)
This gets me a data structure that, while it seems better at first glance, doesn't get me any closer.
[{'agent': 'agent_1', 'qa': [{'qa_status': 'Not Started', 'id__count': 4}, {'qa_status': 'Confirmed', 'id__count': 1}]}, {'agent': 'agent_2', 'qa': [{'qa_status': 'Disqualified', 'id__count': 1}, {'qa_status': 'Not Started', 'id__count': 4}]}]
Ultimately, I want to display the data like so in my html template:
<h1>Agent Summary</h1>
<div class="container">
{% if qas %}
<table class="table">
<thead>
<th scope='col'>Agent</th>
<th scope='col'>Not Started</th>
<th scope='col'>Pending Review</th>
<th scope='col'>Disqualified</th>
<th scope='col'>Confirmed</th>
</thead>
<tbody>
{% for qa in qas %}
<tr>
<td>{{ qa.agent }}</td>
<td>{{ qa.not_started }}</td>
<td>{{ qa.pending_review }}</td>
<td>{{ qa.disqualified }}</td>
<td>{{ qa.confirmed }}</td>
</tr>
{% endfor %}
</tbody>
</table>
{% else %}
{% endif %}
No errors, but I'm not able to pivot these structures into the tabular form I am looking for.