CODEWITHSUNDEEP
pythondjangodjango-models
Naresh
Naresh

Reputation: 1962

Annotate in Same model giving unexpected result

I have user model with below fields..

class User(models.Model):
    user_id = models.CharField(max_length=40,unique=True)
    user_name = models.CharField(max_length=40)
    user_email = models.EmailField()
    user_city = models.CharField(max_length=40)
    class Meta:
        ordering = ['user_id']
        verbose_name = 'User MetaData'
        verbose_name_plural = 'Users MetaData'
    def __unicode__(self):
        return self.user_id

Now i want to filter top 10 cities by user i.e list of top 10 cities with number of users in that city. I used below syntax but always i am getting city_count = 1..

User.objects.annotate(city_count=models.Count('user_city')) .order_by('-city_count'))[:10]

i also tried below syntax but same result...

User.objects.values('user_city').annotate(city_count=models.Count('user_id')) .order_by('-city_count'))[:10]

Where i am doing wrong??

Upvotes: 1

Views: 60

Answers (2)

f43d65
f43d65

Reputation: 2302

Your second example is right, just fix typos. Replace

User.objects.values('user_city').annotate(city_count=models.Count('user_id')) .order_by('-city_count'))[:10]

with

User.objects.values('user_city').annotate(city_count=models.Count('user_city')).order_by('-city_count')[:10]

UPDATE 1, 2:

top_cities_queryset = (User.objects
                       .values('user_city')
                       .annotate(user_city_count=models.Count('user_city'))
                       .order_by('-user_city_count')
                       .values_list('user_city', 'user_city_count'))

top_cities = [{city: count} for city, count in top_cities_queryset]

Upvotes: 1

schillingt
schillingt

Reputation: 13731

I don't think you can do this without a a model that has a relationship to User. Basically you'd need to run the query on another model, then annotate the user_city from that other model. It's a bit weird for your specific case.

However, you can do this in Python. Here's how I'd do it:

from collections import Counter
city_counter = Counter(User.objects.values_list('user_city', flat=True))
top_ten_cities_and_counts = city_counter.most_common()[:10]
top_ten_cities = [city for city, count in top_ten_cities_and_counts]

Upvotes: 2

Related Questions