Need Help With Using Annotate in a Query

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.

Anyone have any guidance to share?

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

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