Django filter and annotate

Question

I have a table called User which is defined like this:

IAM_CHOICES = (
    ('HomeOwner', 'HomeOwner'),
    ('Architect', 'Architect'),
    ('Interior Designer', 'Interior Designer'),
)

class User(AbstractBaseUser, PermissionsMixin):
    first_name = models.CharField(_('First Name'), max_length=30, blank=True, null=True)
    middle_name = models.CharField(_('Middle Name'), max_length=30, blank=True, null=True)
    last_name = models.CharField(_('Last Name'), max_length=30, blank=True, null=True)
    about_yourself = models.TextField(null=True, blank=True)
    image = models.ImageField(upload_to=get_upload_path, null=True, blank=True)
    email = models.EmailField(_('Email Address'), unique=True,
        help_text=_('Please use your personal Email Address, not your Company or College'))
    address = models.TextField(null=True, blank=True)
    city = models.ForeignKey('common.City', null=True, blank=True)
    state = models.ForeignKey('common.State', null=True, blank=True)
    Iam = models.CharField(max_length=50, choices = IAM_CHOICES, null=True, blank=True)

and city table defined like this:

class City(models.Model):
    name = models.CharField(max_length=100)

How do i find city most users are from and are of certain Iam choice of ? i.e say for example find me the cities from where maximum architect (Iam choice type Architect) come from !

Try City.objects.filter(user__Iam='Architect').annotate(num_user=Count('user')).order_by('-num_user'). — Bibhas Debnath
– Bibhas Debnath, Commented Mar 13, 2014 at 10:43

Bibhas Debnath · Accepted Answer · 2014-03-13 11:00:32Z

1

You can order the cities with maximum number of Architects like this -

City.objects.filter(user__Iam='Architect').annotate(num_user=Count('user')).orde‌r_by('-num_user')

This first filters cities that have architects, then counts the architects and then order by them in descending order.

answered Mar 13, 2014 at 11:00

Bibhas Debnath

15k18 gold badges70 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django filter and annotate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related