CODEWITHSUNDEEP
djangodjango-modelsdjango-queryset
Dharanidhar Reddy
Dharanidhar Reddy

Reputation: 878

Aggregation along with extra values

I'm trying to get the aggregate along with its id. I have a model called Index.

class Index(Model):
    correlation = DecimalField()
    company = CharField()


------------------------------
| id | correlation | company |
------------------------------
| 1  | 0.99        | A       |
| 2  | 0.43        | A       |
| 3  | 0.67        | B       |
| 4  | 0.94        | B       |
| 5  | 0.23        | C       |
------------------------------

I need my query to return 

[
 {
  'id': 1,
  'correlation': 0.99,
  'company': 'A'
 },
 {
  'id': 4,
  'correlation': 0.94,
  'company': 'B'
 },
 {
  'id': 5,
  'correlation': 0.23,
  'company': 'C'
 }
]

Explanation: Since Company A has a Maximum correlation of 0.99 at id 1 it is returned. So Basically I need to retrieve id of max correlation for all the companies.

I tried

Index.objects.values('company').annotate(correlation_max = Max('correlation')). Index.objects.values('company', 'id').annotate(correlation_max = Max('correlation')).

None of them are working. I couldn't able to get the id.

I checked this. but I need id as well.

Upvotes: 1

Views: 37

Answers (1)

dirkgroten
dirkgroten

Reputation: 20682

You can select all items with the same company, order them by descending correlation and just pick the first one (with the highest correlation):

from django.db.models import OuterRef, Subquery

company_correlations = Index.objects.filter(company=OuterRef('company')).order_by('-correlation')
max_correlations = Index.objects.filter(id=Subquery(company_correlations.values('id')[:1])).values('id', 'correlation', 'company')

Not very elegant, I'm sure there must be a more elegant way, but I've tested this and it works.

Upvotes: 2

Related Questions