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!

Kedar · Accepted Answer · 2015-03-06T08:29:17.937

5

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

edited Mar 06 '15 at 08:29

answered Mar 06 '15 at 08:03

Kedar

1,648
10
20

Thanks for excellent solution, can you help how can I get all the columns from user table instead only name column – Gowri Mar 06 '15 at 08:23
I have updated my post to answer your other question. – Kedar Mar 06 '15 at 08:29
Not that I know of, you have to explicitly specify all the properties you need. – Kedar Mar 06 '15 at 08:32
The answer was very helpful, just extending question is it possible to do a coulmn alise **userId__name** is looks weired I want to make **name** since I am exposing it in api. so can django allow us to do something like **userId__name as name** – Gowri Mar 12 '15 at 09:51
There is a way to alias but unfortunately it doesn't go through relations as described in the comments here (http://stackoverflow.com/a/10599077/1268926). You can write your own function that renames all keys. `api_return = [{'name':k['userId__name']} for k in v]` where v is the last query in my answer. – Kedar Mar 12 '15 at 10:01

maennel · Answer 2 · 2015-03-06T08:21:10.720

-1

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

edited Mar 06 '15 at 08:21

answered Mar 06 '15 at 08:09

maennel

1,917
1
12
8

`len` will not work on `user.job_set`. You need to do `user.job_set.count()` or `len(user.job_set.all())`. Even then, this method is inefficient as you get all properties of the User object (with a filter) and then all related jobs with `user.job_set` (when you just need the count). – Kedar Mar 06 '15 at 08:18
True, thanks for pointing this out the counting stuff. I agree on your performance remark. However, programatically, this is probably the most easy thing. – maennel Mar 06 '15 at 08:23
1

Programatically yes, scalability-wise not so good when you have lots of rows in your db and / or several columns. – Kedar Mar 06 '15 at 08:35

How to create left join with group by django model query

Mysql query

Django Model

2 Answers2