Django: Select count of foreign key field

Viewed 28

I have the following models:

class SystemTable(models.Model):
    id = models.FloatField(primary_key=True)
    system_name = models.CharField(max_length=50)
    system_desc = models.CharField(max_length=200, blank=True, null=True)
    system_url = models.CharField(max_length=2000, blank=True, null=True)
    status = models.ForeignKey(StatusLkp, models.DO_NOTHING, db_column='status',default=1)
    date_created = models.DateTimeField(default=datetime.now())
    objects = models.Manager()

    class Meta:
        app_label = 'system_table'
        managed = True
        db_table = 'system_table'


class SystemPagesTable(models.Model):
    id = models.FloatField(primary_key=True)
    system = models.ForeignKey(SystemTable, on_delete=models.CASCADE)
    page_name = models.CharField(max_length=200)
    page_link_xpath = models.CharField(max_length=2000)
    flag = models.FloatField(blank=True, null=True)
    status = models.ForeignKey(StatusLkp, models.DO_NOTHING, db_column='status',default=1)
    date_created = models.DateTimeField(default=datetime.now())
    objects = models.Manager()

    class Meta:
        app_label = 'system_pages_table'
        managed = True
        db_table = 'system_pages_table'

I want to perform the following SQL query:

select
  s.*,
  (select count(*) from page p where p.system_id = s.id) as NUM_OF_PAGES
from system s;

How do I perform the above query with Django's ORM without having to use a for loop?
I do not want a result based on one system, I want to retrieve all systems with their page counts.

1 Answers

Try to use annotate:

from django.db.models import Count
System.objects.annotate(num_pages=Count('page'))
Related