How to create left join with group by django model query

Question

Hi I declared django model as below, I just want to run simple left join with group by query.

Mysql query

SELECT  u.name, COUNT('j.*') as num_jobs FROM `User` as u 
LEFT JOIN Job as j ON u.id = j.userId_id GROUP BY j.userId_id

The above query is getting job count of each user.

Django Model

from django.db import models


class Users(models.Model):       
    name = models.CharField(max_length=60,verbose_name="Name",null=True, blank=True)
    email_id = models.CharField(max_length=60,verbose_name="Email Id",null=True, blank=True)
    phonenumber = models.CharField(max_length=20, verbose_name="Phone Number")
    class Meta:
        verbose_name_plural = "Users"
        verbose_name = "Users"
        db_table = "User"
    def __unicode__(self):
        return  str(self.phonenumber) + ": "+ str(self.name)  

class Job(models.Model):
    userId = models.ForeignKey(Users)
    title = models.CharField(max_length=128,verbose_name="Title")
    description = models.TextField(verbose_name="Description",null=True, blank=True)
    class Meta:
        verbose_name_plural = "Job"
        verbose_name = "Job"
        db_table = "Job"
    def __unicode__(self):
        return  self.title

Do I need to adjust anything in model, please guide me!

Answer 1

Try this

from django.db.models import Count

# import your model (Job)

Job.objects.values('userId').annotate(c=Count('userId')).values('userId__name','c')

The initial values('userId') followed by annotate will count and group by userId.

The output will be a list of dicts like this:

[{'userId__name':'John','c':20},]

Where the user John has 20 related Job objects.

On an unrelated note, whenever you have foreign keys in your models. It is usually better to write user instead of userId.

It makes more sense when you write queries and access relations (user__name instead of userId__name).

UPDATE:

If you need other properties, add them as arguments to the second values call like this:

Job.objects.values('userId').annotate(c=Count('userId'))\
.values('userId__name','userId__email_id','c')