Django view filter count

user_16951143 0
Viewed 47

I want to get to know the number 'tasks' (Pendentes) for each 'Customer' (Cliente)

I have this models in my models.py:

class Cliente(models.Model):
    id = models.IntegerField(primary_key=True)
    nome = models.CharField(max_length=200, help_text="Nome")
    localidade = models.CharField(max_length=100, help_text="Localidade")
    [...]

class Pendente(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, help_text="ID Unico")
    cliente = models.ForeignKey('Cliente', on_delete=models.RESTRICT, blank=True, null=True)
    memo = models.TextField(null=True, blank=True, help_text="Memória Descritiva", max_length=200)
    criado = models.DateField(default=date.today, null=True, blank=True, help_text="Data registo ")
    concluir = models.DateField(null=True, blank=True, help_text="Data de execução máxima ")
    tecnico = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True)
    tecnicoatribuido = models.ForeignKey('TecnicoAtribuido', on_delete=models.SET_NULL, blank=True, null=True)

Basicly i want to run this sql:

"select A.id, A.nome, A.localidade, count(B.id) as tarefas
from todolist_pendente B 
LEFT JOIN todolist_cliente A
ON A.id = B.cliente_id
GROUP by A.id;"

Can't seem to figure out how it would work on a view.

I'm able to get the count per cliente with :

resultado = Pendente.objects.values('cliente').order_by('cliente').annotate(count=Count('id'))

But this doesn't render the name of each customer on the template.

On my template I'm using :

  <ul>
    {% for obj in resultado %}
    <li>{{ obj.cliente}} : {{ obj.count}}</li>
    {% endfor %}
  </ul>

This is rendering:

6 : 2
35 : 1
40 : 1
92 : 1
106 : 1
105 : 1

is there a way to get the foreignkey value 'nome' so that it could be rendered like this:

id  Nome    Localidade  Tarefas
6   Cliente A   Quarteira   2
35  Cliente B   Lisboa  1
40  Cliente C   Barreiro    1
92  Cliente D   Lisboa  1
105 Cliente E   Faro    1
106 Cliente F   Barreiro    1

Help would be appreciated...

django-views django
3 Answers

You can try this approach:

class Cliente(models.Model):
    id = models.IntegerField(primary_key=True)
    nome = models.CharField(max_length=200, help_text="Nome")
    localidade = models.CharField(max_length=100, help_text="Localidade")
    [...]

    @property
    def pendente_count(self):
      return Pendente.objects.filter(cliente=self).count()


# from client object now you can access pendente_count property

# If you pass client queryset context with your template
queryset = Cliente.objects.all()

# inside template
{% for client in queryset %}
  {{ client.nome }}  {{ client.pendente_count }}
{% endfor %}


# or you can use this without adding a new property in you model
{{ client.pendente_set.all.count }}
user_109569910

You can annotate each Cliente with the number of related Pendente with:

from django.db.models import Count

resultado = Cliente.objects.annotate(
    pendente_count=Count('pendente')
)

you can also filter the Clients, such that these should at least have one Pendente:

from django.db.models import Count

resultado = Cliente.objects.annotate(
    pendente_count=Count('pendente')
).filter(pendente_count__gt=0)

This will hit the database with one query, and thus let the database count the number of related Pendente, per Cliente.

then in your template, you can render this with:

<table>
  <thead>
    <tr>
      <th>id</th><th>Nome</th><th>Localidade</th><th>Tarefas</th>
    </tr>
  </thead>
  <tbody>
    {% for cliente in resultado %}
        <tr><td>{{ cliente.id }}</td><td>{{ cliente.nome }}</td><td>{{ cliente.localidade }}</td><td>{{ cliente.pendente_count }}</td></tr>
    {% endfor %}
  </tbody>
</tble>
user_675790

Thanks for the help! Fixed it with it the following:

views.py :

resultado = Cliente.objects.annotate(pendente_count=Count('pendente')

Template (only wanted the Clientes that had Pendentes so I added an if clause):

    <tbody>
  {% for cliente in resultado3 %} {% if cliente.pendente_count > 0 %}
  <tr>
    <td>{{ cliente.id }}</td>
    <td>{{ cliente.nome }}</td>
    <td>{{ cliente.localidade }}</td>
    <td>{{ cliente.pendente_count }}</td>
  </tr>
  {% endif %} {% endfor %}
</tbody>

Thank you ! I was feeling like an Hamster on a well :)

user_169511430
Related