python - 具有JOIN和GROUP BY SQL查询的Django COUNT

对于这个SQL查询，正确的Django视图和HTML是什么？:

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模板呢？
谢谢您！

最佳答案

查看有关聚合的文档：
https://docs.djangoproject.com/en/1.6/topics/db/aggregation/
您应该能够编写一个返回queryset的视图，其计数类似于：

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

至于HTML，这完全取决于您。不过，如果您提供这个queryset，您就可以使用{{ object.bus_count }}获取计数。

关于python - 具有JOIN和GROUP BY SQL查询的Django COUNT，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22414042/