CODEWITHSUNDEEP
pythondjangodjango-models
Gowri
Gowri

Reputation: 16835

How to create left join with group by django model query

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!

Upvotes: 6

Views: 4702

Answers (2)

Kedar
Kedar

Reputation: 1698

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')

Upvotes: 5

maennel
maennel

Reputation: 1985

I think the most straight-forward solution would be to get your User objects by:

users = User.objects.filter(<filtering-expression-here>)

Once you've got the users, you can consider the number of jobs he has by:

for user in users:
    print "User '%s' has %s jobs" % (user.name, str(user.job_set.count()))

PS: This suggestion assumes that you rename your 'Users' model to 'User' (without the s)

EDIT: I changed len(...) to .count()

Upvotes: -2

Related Questions