How can I translate SQL query to to django ORM

I’m working on making some data consults in Django but I don’t understand quite well its ORM system yet. I need to get the transactions’ quantity for a particularly transaction’s projections. To put it briefly, I want to translate this SQL query to Python/Django syntax:

select cp.name, count(*) as total
from core_transaction ct 
inner join core_projection cp 
on ct.projection_id = cp.id
group by cp.name

JavaScript
​x
 
select cp.name, count(*) as total
from core_transaction ct 
inner join core_projection cp 
on ct.projection_id = cp.id
group by cp.name
​

These are the models involved:

class Transaction(models.Model):
    date = models.DateField()
    projection = models.ForeignKey('Projection', blank=False, null=False, related_name='transactions')

class Projection(models.Model):
    name = models.CharField(max_length=150)
    description = models.CharField(max_length=300, blank=True, null=True)

JavaScript
 
class Transaction(models.Model):
    date = models.DateField()
    projection = models.ForeignKey('Projection', blank=False, null=False, related_name='transactions')
​
class Projection(models.Model):
    name = models.CharField(max_length=150)
    description = models.CharField(max_length=300, blank=True, null=True)
​

Answer

You do this with an [.annotate(…) clause [Django-doc]]:

from django.db.models import Count

Projection.objects.annotate(
    total=Count('transactions')
)

JavaScript
 
from django.db.models import Count
​
Projection.objects.annotate(
    total=Count('transactions')
)

The Projection objects that arise from this queryset will have an extra attribute .total that contain the number of Transactions.

Advertisement

Answer