How to perform group by on couple of columns and fetch multiple columns from multiple table using Django ORM

Using Django Using the ORM
21

I mean, how does your current django query’s like?

Like this one:

22

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”), job__name=F(“job__name”), company__name=F(“company__name”), month=F(“month”), year=F(“year”), date=F(“date”), company__name=F(“company__beta__name”))
.order_by(“job__id”, “company__beta__id”)

response of qs.query.str() is

SELECT employee.job_id, SUM(employee.total_tasks) AS total_task, SUM(employee.today_tasks) AS today_task, job.name AS job__name, company.name AS company__name, employee.month AS month, employee.year AS year, employee.date AS date, beta.name AS beta_name FROM employee LEFT OUTER JOIN job ON (employee.job_id = job.id) LEFT OUTER JOIN company ON (employee.company_id = company.id) LEFT OUTER JOIN beta ON (company.beta_id = beta.id) WHERE employee.date = 2023-02-23 GROUP BY employee.job_id, job.name, company.name, employee.month, employee.year, employee.date, beta.name ORDER BY employee.job_id ASC

23

Maybe try preceding the annotate clause with the order_by clause.

qs = Employee.objects.filter(date=‘2023-02-23’).select_related(“job”, “company”, “beta”).values(“job_id”).order_by(“job__id”, “company__beta__id”).annotate(total_task=Sum(“total_tasks”), today_task=Sum(“today_tasks”), job__name=F(“job__name”), company__name=F(“company__name”), month=F(“month”), year=F(“year”), date=F(“date”), company__name=F(“company__beta__name”))
24

Hi @leandrodesouzadev

Not working :frowning:

25

If none of the methods works for you. Try the raw approach

26

Side note: You should be less concerned about precisely matching the specific SQL statement generated by the ORM and focused more on the results. It may be helpful if you identified how these queries are not providing the proper results.
It particular, it may be more useful to define what you’re looking to generate as the output, rather than trying to strictly replicate the SQL. It’s possible that there’s a different approach available to do what you’re looking to do.

27

Currently I’m following Raw SQL way only, But I feel ORM is Good choice over Raw SQL

28

Hi @kenWhitesell,

Thank your for the response, I earlier shared sql query and asked for help to replicate like this using ORM because I done the same thing with Raw SQL But couldn’t make it with ORM. I just want to replace Raw SQL with ORM query set.

29

Is it possible with Django ORM ?

30

Please define what you’re looking for in terms of the results you want to see, not by trying to replicate an existing SQL query.

For example, what I see from your original post:

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;

So, I’m a little confused here.

Are you looking to generate 1 row of output for each combination of (job, beta), with the identified sums of all Employees having that same (job, beta)?

Or are you looking to generate multiple rows of output for each Employee, where each row is some pairing of (job, beta) for that employee?

What’s specifically getting me confused here is that you’re combining an aggregate result with the retrieval of non-aggregated columns. The values of sum(total_tasks) and sum(today_tasks) imply the retrieval of multiple Employee rows, but the references to fields such as year, month, etc are references to single rows.

31

Are you looking to generate 1 row of output for each combination of (job, beta), with the identified sums of all Employees having that same (job, beta)

Yes I want single row for same (job, meta)

32

So the problem with this query as stated is that you’re also looking to retrieve a year, month, and company.name - but that aggregation is an aggregate of multiple Employee rows, and so the query engine would not be able to uniquely identify which of the Employee rows to use to retrieve those values.

Even if your current data would all result in the same data being retrieved, the fact that this isn’t required by the database is enough to identify this query as being malformed.

33

Hi @KenWhitesell,

But I want retrieve month, year and company.name values also, I can understand that if we won’t mentioned additional columns like month, year and company.name we will get query like group by on columns which we mentioned in values followed by annontate function. But as per my requirement I need like that, and I just want to know is it possible through ORM in any other possible ways.

34

You need to define what it is that you want. What you’re asking for with the information that you’ve provided doesn’t make sense the way you’re asking for it.

Given that you are going to want multiple rows of data, where each row is a unique (job, beta) pair, which “month”, “year”, and “company.name” do you want on those rows?

The problem is that there isn’t one “month”, “year”, or “company.name” associated with a (job, beta) pair.

35

“”“”“”“”"

Here I’ve 2 requirements

  1. I want group by on job_id and beta_id (When this happened I want Company Name should be hard coded like ‘X’, because company names are multiple but when I used Meta, A meta can contains multiple Companies and I want all company as single)
  2. I want group by on job_id as unique in this case (When this happened I want beta Name should be hard coded like ‘hardcodedBeta’, because beta names)