Skip to content
Advertisement

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

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)

Advertisement

Answer

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

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