Need Help With Using Annotate in a Query

Using Django
1

Hi all,

Standard newbie preamble.

I have the following query …

collections_cities = Collection.objects.filter(city_id__gt=0).select_related(‘city’).annotate(Count(
‘city__display_name’, distinct=True)).order_by(‘city__display_name’)

Based on the following models:

class Collection(models.Model):
        city = models.ForeignKey(City, blank=True, null=True, on_delete=models.CASCADE, verbose_name='Location: City')

I’m using the django-cities-light package:

class City(models.Model):
    name = models.CharField(max_length=200, db_index=True)
    display_name = models.CharField(max_length=200)
    name_ascii = models.CharField(max_length=200, blank=True, db_index=True)
    slug = autoslug.AutoSlugField(populate_from='name_ascii')
    geoname_id = models.IntegerField(null=True, blank=True, unique=True)
    alternate_names = models.TextField(null=True, blank=True, default='')

My ultimate goal is to create a display like the following, with the ? replaced by the number of collections under that city name. I think you would use Annotate and Count, but I’m not having any luck. The query above does not perform the equivalent of distinct(), as show in the screenshot.

2020-07-19_capture-01
2020-07-19_capture-011724×838 143 KB

Anyone have any guidance to share?

2

So what you’re really looking to do is get a list of Cities. That’s your starting point:

collections_cities = City.objects

now, what you want to do is get a count of how many collections are related to that city:

.annotate(num_collection=Count('collection'))

but, you only want cities with collections:

.filter(num_collection__gt=0)

and you wanted sorted by the name of the city:

.order_by('display_name')

giving you:
collections_cities = City.objects.annotate(num_collection=Count('collection')).filter(num_collection__gt=0).order_by('display_name')

which is a query set yielding one entry per city, with an attribute named num_collection containing the number of collections related to that city.

Ken

3

Ken, wow, you know the closing credits at the end of this project? You’re going to get major billing.

This was really helpful to see your approach, because obviously I did not intuit starting with cities. Yet it make sense. Thank you for educating me.

Sam