python - Django COUNT avec requête JOIN et GROUP de SQL - Stack Overflow

What would be the correct Django view and HTML for this SQL query?:

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 ;

My Models are:

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")
....

And the HTML Template?

Thank you!

Check out the docs on aggregration: https://docs.djangoproject.com/en/1.6/topics/db/aggregation/

You should be able to write a view that returns a queryset with counts similar to this:

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

As for the HTML, that's entirely up to you. If you provide that queryset, though, you'd be able to get the count with {{ object.bus_count }}.

What would be the correct Django view and HTML for this SQL query?:

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 ;

My Models are:

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")
....

And the HTML Template?

Thank you!

Check out the docs on aggregration: https://docs.djangoproject.com/en/1.6/topics/db/aggregation/

You should be able to write a view that returns a queryset with counts similar to this:

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

As for the HTML, that's entirely up to you. If you provide that queryset, though, you'd be able to get the count with {{ object.bus_count }}.

Source