Get top rows by one column Django
I'm making a job to categorize earnings and expenses, from app of movimentations. To this, i need get the category to tile, with more cases.
For example, in this Scenario:
| title | category | count |
| ----- | -------------- | ----- |
| Pizza | food | 6 |
| Pizza | others_expense | 1 |
| Pizza | refund | 1 |
I want return just the first row, because the title is the same, and category food
is used with most frequency.
Code example
I want get the result using just Django ORM, because i have diferent databases and is more fast than iterate over a large list.
Model:
class Movimentation(models.Model):
title = models.CharField(max_length=50)
value = models.FloatField()
category = models.CharField(max_length=50)
Consult:
My actual consult in Django ORM is.
Movimentation.objects \
.values('title', 'category') \
.annotate(count=Count('*')).order_by('title', '-count')
Result:
[
{'title': 'Pizza', 'category': 'food', 'count': 6},
{'title': 'Pizza', 'category': 'others_expense', 'count': 1},
{'title': 'Pizza', 'category': 'refund', 'count': 1},
{'title': 'Hamburguer', 'category': 'food', 'count': 1},
{'title': 'Clothing', 'category': 'personal', 'count': 18},
{'title': 'Clothing', 'category': 'home', 'count': 15},
{'title': 'Clothing', 'category': 'others_expense', 'count': 1}
]
Expected result:
In this case, i get just one row by title, with the most used category.
[
{'title': 'Pizza', 'category': 'food', 'count': 6},
{'title': 'Hamburguer', 'category': 'food', 'count': 1},
{'title': 'Clothing', 'category': 'personal', 'count': 18}
]