CODEWITHSUNDEEP
pythonsqldjangoorm
jat
jat

Reputation: 85

How to change join and group by SQL to ORM in Django

I'm new in Django. So, I want to join two models which are company and client and count the number of clients for each of the company. Here the SQL

SELECT Company_company.name, count(Client_client.cid)
FROM Company_company
LEFT JOIN Client_client
ON Company_company.comid = Client_client.comid_id
GROUP BY Company_company.name;

But since in Django, we use ORM. So I'm a little bit confusing since I'm a beginner. I already refer few SQL to ORM converter website such as Django ORM and do some try and error. But, I didn't know where the problem since I want the output from the ORM to be classified into a different array. Here is my code:

labels = []
data = []

queryClientCompany = client.objects.values('comid').annotate(c=Count('cid')).values('comid__name','c')

for comp in queryClientCompany:
    labels.append(comp.comid__name)
    data.append(comp.c)

Here some of the relevant things in the client and company models:

class client (models.Model):
    
    #client info
    cid = models.AutoField(primary_key = True)
    comid = models.ForeignKey(company,related_name='companys',
            on_delete = models.DO_NOTHING,verbose_name="Company",null = True, blank = True)


class company(models.Model):

    comid = models.AutoField(_('Company'),primary_key = True)

    #company info
    name = models.CharField(_('Company Name'),max_length = 50)

The error stated that the comid__name is not defined. So actually how to append the result? I hope someone can help me. Thank you for helping in advanced.

Upvotes: 1

Views: 123

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476584

You should query from the opposite side to perform the LEFT OUTER JOIN between company and client (and not client and company):

from django.db.models import Count

labels = []
data = []

queryClientCompany = company.objects.annotate(
    c=Count('companys__cid')
)

for comp in queryClientCompany:
    labels.append(comp.name)
    data.append(comp.c)

The companys part is due to the related_name='copanys', but it does not make much sense to name this relation that way. The related_name=… parameter [Django-doc] specifies how to access the Clients for a given Company, so clients is a more appropriate value for the related_name:

class client (models.Model):
    cid = models.AutoField(primary_key=True)
    comid = models.ForeignKey(
        company,
        related_name='clients',
        on_delete = models.DO_NOTHING,
        verbose_name="Company",
        null = True,
        blank = True
    )

then the query is:

from django.db.models import Count

labels = []
data = []

queryClientCompany = company.objects.annotate(
    c=Count('clients__cid')
)

for comp in queryClientCompany:
    labels.append(comp.name)
    data.append(comp.c)

Upvotes: 1

Related Questions