Django中带JOIN和GROUP BY的COUNT SQL查询

Question

这个问题是在问，怎样写出正确的Django视图和HTML代码来实现这个SQL查询？

SELECT 
  hood.`hood`,
  COUNT(business.`id`) AS TOTAL 
FROM
 `hood` 
JOIN business 
  ON hood.`id` = business.`hood_id` 
WHERE business.`city_id` = 8 
GROUP BY hood.`id` 
ORDER BY TOTAL DESC 
LIMIT 5 ;

我的模型是：

class Hood(models.Model):
    name = models.CharField(max_length=50, db_column='hood')
    slugname = models.SlugField(max_length=50, blank=True)
    city = models.ForeignKey('City', related_name='hoods')
    location = models.ForeignKey('Location', related_name='hoods')
    switch = models.SmallIntegerField(null=True, blank=True, default='1')
    class Meta:
        db_table = 'hood'  


class Business(models.Model):
    name = models.CharField(max_length=50, db_column='name', blank=True)
    slugname = models.SlugField(max_length=50, blank=True)
    city = models.ForeignKey('City', related_name="business")
    hood = models.ForeignKey('Hood', null=True, blank=True, related_name="business")
    ....

那HTML模板应该怎么写呢？

谢谢！

Answer 1

可以看看关于聚合的文档：

https://docs.djangoproject.com/en/1.6/topics/db/aggregation/

你应该能写一个视图，返回一个包含计数的查询集，类似下面这样：

from django.db.models import Count
Hood.objects.filter(business__city_id=8).annotate(bus_count=Count('business__id'))

至于HTML部分，那完全看你自己怎么做。不过，如果你提供了那个查询集，你就可以用 {{ object.bus_count }} 来获取计数。