Django Queryset Rendering in html template

Python
1

Hi guys, am a Django beginner here. So, I have this table that I want to render on a bootstrap template to display managers name and their respective number of clients linked to them in a loop that displays as a table.

Here is my view in Django:

def managers(request):

managers = Manager.objects.all()

total_clients = Manager.objects.annotate(clients_count=Count('client'))

context = {'managers': managers, 'total_clients': total_clients}

return render(request, 'hello.html', context)

Here is my Html template code:

                        <thead class="thead-light">

                            <tr>

                                <th>Name</th>                                                                  

                                <th>No of Clients</th>                                    

                               

                            </tr>

                        </thead>

                        <tbody>

                            {% for Manager in managers %}

                                                                               

                                    <tr>                                            

                                        <td>{{Manager.name}}</td>                                            

                                        <td>{{total_clients}}</td>

                                                                                               

                                    </tr>

                               

                            {% endfor %}

                                                                                           

                        </tbody>

                    </table>

Here is the result I get on browser:

Name No of Clients
Farai1 <QuerySet [<Manager: Farai1>, <Manager: Farai2>, <Manager: Farai3>, <Manager: Farai4>]>
Farai2 <QuerySet [<Manager: Farai1>, <Manager: Farai2>, <Manager: Farai3>, <Manager: Farai4>]>
Farai3 <QuerySet [<Manager: Farai1>, <Manager: Farai2>, <Manager: Farai3>, <Manager: Farai4>]>
Farai4 <QuerySet [<Manager: Farai1>, <Manager: Farai2>, <Manager: Farai3>, <Manager: Farai4>]>

Here is a result from mySQL Debugger:

SQL queries from 1 connection

  • default 5.00 ms (7 queries including 4 similar and 4 duplicates )

Query Timeline Time (ms) Action

  • SELECT ••• FROM “django_session” WHERE (“django_session”.“expire_date” > ‘’‘2021-11-21 10:30:21.068018’’’ AND “django_session”.“session_key” = ‘’‘vz558fl6sj2vvc4qpl5fcsgc4546krle’’’) LIMIT 21 2.00
  • SELECT ••• FROM “auth_user” WHERE “auth_user”.“id” = ‘1’ LIMIT 21 0.00
  • SELECT ••• FROM “table_manager” 1.00
  • SELECT ••• FROM “table_manager” LEFT OUTER JOIN “table_client” ON (“table_manager”.“id” = “table_client”.“manager_id”) GROUP BY “table_manager”.“id”, “table_manager”.“name” LIMIT 21

4 similar queries. Duplicated 4 times. 1.00

  • SELECT ••• FROM “table_manager” LEFT OUTER JOIN “table_client” ON (“table_manager”.“id” = “table_client”.“manager_id”) GROUP BY “table_manager”.“id”, “table_manager”.“name” LIMIT 21

4 similar queries. Duplicated 4 times. 0.00

  • SELECT ••• FROM “table_manager” LEFT OUTER JOIN “table_client” ON (“table_manager”.“id” = “table_client”.“manager_id”) GROUP BY “table_manager”.“id”, “table_manager”.“name” LIMIT 21

4 similar queries. Duplicated 4 times. 1.00

  • SELECT ••• FROM “table_manager” LEFT OUTER JOIN “table_client” ON (“table_manager”.“id” = “table_client”.“manager_id”) GROUP BY “table_manager”.“id”, “table_manager”.“name” LIMIT 21

4 similar queries. Duplicated 4 times. 0.00

…

SQL selected

Executed SQL

SELECT “table_manager”.“id”,
“table_manager”.“name”,
COUNT(“table_client”.“id”) AS “clients_count”
FROM “table_manager”
LEFT OUTER JOIN “table_client”
ON (“table_manager”.“id” = “table_client”.“manager_id”)
GROUP BY “table_manager”.“id”,
“table_manager”.“name”
LIMIT 21

Time

1.0001659393310547 ms

Database

default

ID NAME CLIENTS_COUNT
1 Farai1 2
2 Farai2 2
3 Farai3 0
4 Farai4 0

Django table sql queryset
Django table sql queryset1365×506 29.2 KB

I want to pull out that filed CLIENTS_COUNT onto the bootstrap template as a number like the way it displays in the SQL debugger and not how its coming out on the browser. Your Assistance is Greatly Appreciated!

Sorry for the presentation I just copied and pasted, tried to put screenshots but the platform wont allow to embed more than 1 pic

2

You need to pass the client count property for each result in the QuerySet.

total_clients[0].clients_count
total_clients[1].clients_count
# You get the idea....
3

Thank you for the response, however it is not giving me result as it appears in the SQL debugger. I am trying to have the number of Clients associated with each Manager in a loop that displays like a table

I tried your suggestion and got this:

Name No of Clients
Farai1 2
Farai2 2
Farai3 2
Farai4 2

which is not the case because Managers number 3 and 4 have no client associated to them

4

Think about it. If your SQL query is correct but what you pull out from the query is wrong, then where is the problem?

If you used something like total_clients[0].clients_count in your markup, this means you only want clients_count from row 1. So, of course, you will get a result like that.

In your case, you don’t need managers because total_clients already incudes managers.
So, iterate over total_clients and access it like so

{% for manager in total_clients %}
    {manager.name}
    {manager.clients_count}
{% endfor %}
1 Like
5

Thank you so much, its now working so my loop had an error!

Much Appreciated

6

Django’s templates are not simply Python code embedded into HTML . 2) an
then renders it to a template with the QuerySet added to the context (line 5).

PrepaidCardStatus