python – Filter and sort by largest number of inhabitants

Question:

Greetings to all.

I am working with Django and I need to obtain a list with the cities ordered by the one with the most inhabitants, here I leave the models that I am using.

class Ciudad(models.Model):
    codigo = models.CharField(max_length=4,)
    nombre = models.CharField(max_length=128)

class Persona(models.Model):
    ciudad_residencia = models.ForeignKey(Ciudad, null=True, blank=True,
                        verbose_name=_("ciudad de residencia*"), on_delete=models.PROTECT)

The idea is to be able to obtain the name of the 5 cities with the highest number of people and show the name of the city and next to that the number of people in a table in reporlab , that's why I store it in a list.

I am using a for loop but I would like to know if there is a more optimal way to do it, following the code of the walkthrough.

ciudades = Ciudad.objects.all()
personas = Personas.objects.all()
contador_registros = 0
for ciudad in ciudades:
    personas_ciudad = personas.filter(ciudad_residencia=ciudad)
    if personas_ciudad.count() > 0:
        tabla_personas_ciudad.append(
            [ciudad.nombre,
             personas_ciudad.count()
             ]
        )
        contador_registros = contador_registros + 1
    if contador_registros >= 5:
        break

Also I do not know how to order it from the largest to the smallest number of people in the city.

Answer:

Assuming you want to do the following query:

SELECT ciudad.nombre, count(personas.id_ciudad) as num_habitantes 
  FROM ciudad 
 INNER JOIN personas 
    ON personas.id_ciudad = ciudad.id 
 GROUP BY (ciudad.nombre) 
 ORDER BY count(personas.id_ciudad);

Django has Aggregation functions , in this case we need the Count, your code would be as follows:

from django.db.models import Count
ciudades_con_total_habitantes = Ciudad.objects.annotate(num_habitantes=Count('personas')).order_by('-num_habitantes')

Now you want to see the top 5 cities with the largest inhabitants, previously we order it from highest to lowest, it only remains to cut the result

ciudades_con_total_habitantes[:5]
Scroll to Top