user2216194 user2216194 - 6 months ago 21
SQL Question

Django order by most frequent value

I have a model that represent foodtrucks.

class FoodTruck(models.Model):
name = models.CharField(max_length=30)
location = models.TextField()
creation_date = models.DateTimeField(default=datetime.now)
start_date = models.DateTimeField(null=True, blank=True)

def __unicode__(self):
return self.name


There will be many rows in this table with the same name (but different locations). In my view I want to order the list by name (that has the highest occurrence).

Example Table:

name | location | creation_date | start_date
clover | ABC | ...
chens | XYZ | ...
clover | ABC | ...


My goal is to list the food trucks and how many times they’ve appeared over the last 30 days across all locations, sorted with the most frequent ones first.

Answer

This is a job for aggregation.

from django.db.models import Count
FoodTruck.objects.values_list('name').annotate(truck_count=Count('name')).order_by('-truck_count')
Comments