I have 4 tables
Table A contains name, all_data, today_data, standard_name, table_b_foreign_key, table_c_foreign_key, date
Table B contains name, type, id
Table C contains name, id, table_d_foreign_key
Table D name, id
Now I want below query in DJango ORM queryset form
select B.name, sum(all_data), sum(today_data), standard_name, date, b.type, c.name, d.name from Table A Inner join Table B on Table A.tableb_id=Table B.id Inner join Table A.tablec_id=Table C.id Inner Join Table D on Table c.tabled_id = Table D.id where Table A.date=‘2023-02-22’ group by Table B.id, Table D.id order by Table B.type, Table B.name
Thanks in advance
Not a easy thing to do without seeing the models.
But you’re going to need something like this (this code wont work as-is):
from datetime import date
from django.db.models import Sum
filter_date = date(2023, 2, 22)
qs = (
A.objects.filter(date=filter_date)
.select_related("b", "c", "d") # This is the join. You'll need to use the reverse-lookups, forward-lookups
.values("b__name", "d__id") # This will produce a group by
.annotate(Sum(all_data="all_data"), Sum(today_data="today_data") # This is the aggregation part
.values("standard_name") # Here put all the other fields
.order_by("b__type", "b__name") # finally the order by
)
Here are some links to the documentation:
ForeignKey field
In this, specifically you want to look at this argument: related_name and looking on the making queries for foreign key.
Another useful piece is the aggregation section
@leandrodesouzadev Appreciate for the response, just to confirm one thing in above sample you shared will apply group by on (“b__name”, “d__id”) these two columns or values(“standard_name”) will also include standard_name too.
As i said, you should not expect this to run as-is. I tried to give you an example on how to implement it.
But you can add any other field to the first values call, that will be put on the group by clause.
Yeah, I got it. I will do changes as per my requirement, but just confirmation that those we mentioned in 1st values will consider for group by and next values won’t considered in group by
1 Like
@leandrodesouzadev I just tried the sample you provide above, second values columns also showing in Group BY. I just verified query using qs.query.str().
You’re right, that was my bad.
Take a look on this link from the docs
@leandrodesouzadev
Here I’m sharing my models and required query in queryset format
class Employee(models.Model):
job = models.ForeignKey(Job, models.DO_NOTHING, blank=True, null=True)
total_tasks = models.IntegerField()
today_tasks = models.IntegerField()
year = models.IntegerField()
month = models.CharField(max_length=3)
company = models.ForeignKey(Company, models.DO_NOTHING, blank=True, null=True)
emp_name = models.CharField(max_length=10, blank=True, null=True)
date = models.DateField()
class Job(models.Model):
name = models.CharField(max_length=255, db_collation=‘utf8mb3_unicode_ci’)
role = models.CharField(max_length=13)
date_added = models.DateTimeField()
class Company(models.Model):
name = models.CharField(max_length=255, db_collation=‘utf8mb3_unicode_ci’)
Beta = models.ForeignKey(‘Beta’, models.DO_NOTHING, blank=True, null=True)
class Beta(models.Model):
name = models.CharField(max_length=255, db_collation=‘utf8mb3_unicode_ci’)
Here I want to perform query like below in the form of queryset
select job.name, sum(total_tasks) as total_tasks, sum(today_tasks) as today_tasks, year, month, company.name, beta.name from employee inner join job on employee.job_id=job.id inner join employee.company_id=company.id inner join company.beta_id = beta.id where date=‘2023-02-23’ group by job.id, beta.id order by job.id, beta.id;
Don’t expect me to write the code for you.
Have you read the documentation and understand it? If not i’ll be happy to help you and guiding you into the correct direction.
I’m looking into the document which you shared
I understand below thing from document, please correct me if I’m wrong.
If we specify values before annotate, whatever columns we mentioned in values will act as group by. and we no need to mentioned annotate columns in values explicitly.
If we have annotate before the values, annotate columns explicitly mention in values and that model id acts as group by.
If we use order by with some column names those also acts as group by when we have values before the annotate
@leandrodesouzadev,
Is this valid ?
qs = Employee.objects.filter(date=‘2023-02-23’).select_related(“job”, “company”, “beta”)
.annotate(total_task=Sum(“total_tasks”), today_task=Sum(“today_tasks”))
.values(“job__name”, “company__name”, “month”, “year”, “date”, “company__beta__name”)
.order_by(“job__id”, “company__beta__id”)
You can inspect this using:
sql = str(qs.query)
print(sql)
@leandrodesouzadev,
Yeah, It’s not working as I required. It is including Employee.id in group by
can you help me how to solve it
I think you need to do something like this:
from django.db.models import F
qs = Employee.objects.filter(date=‘2023-02-23’).select_related(“job”, “company”, “beta”).values("job_id").annotate(total_task=Sum(“total_tasks”), today_task=Sum(“today_tasks”), F(“job__name”), F(“company__name”), F(“month”), F(“year”), F(“date”), F(“company__beta__name”))
.order_by(“job__id”, “company__beta__id”)
This is probably generate the right query.
Thanks @leandrodesouzadev, Appreciate your efforts
It’s not working @leandrodesouzadev, It’s including below values also under group by
F(“job__name”), F(“company__name”), F(“month”), F(“year”), F(“date”), F(“company__beta__name”)
How it’s current the queryset?
Hi @leandrodesouzadev,
It is including the columns which we mentioned in values and query results are something like below.
Group by job.id, job_name, company_name, month, year, date, company_name like this