i would like to return a single tuple with, field by field, the most frequent values. My models is as follows:
class myTable(models.Model):
a = models.IntegerField(blank = True, default = 0)
b = models.IntegerField(blank = True, default = 0)
c = models.IntegerField(blank = True, default = 0)
d = models.IntegerField(blank = True, default = 0)
My DB is as follows:
ID | a | b | c | d |
---|---|---|---|---|
0 | 2 | 4 | 1 | 0 |
1 | 3 | 4 | 2 | 3 |
2 | 2 | 3 | 1 | 3 |
3 | 1 | 2 | 6 | 2 |
The single tuple that I want to return is constitued like that: a=2, b=4, c=1, d=3 (a is 2,3,2 and 1 so the most frequent value is 2). How can I do that?
CodePudding user response:
I think you will have to do 4 separate queries:
from django.db.models import Count
fields = 'a', 'b', 'c', 'd'
result = [
{
field_name: (
myTable.objects
.annotate(value=F(field_name)) # rename field for more readable output
.values('value')
.annotate(count=Count('value'))
.order_by('-count')
.first()
)
}
for field_name in fields
]
This will produce following result:
# result
[
{'a': {'value': 2, 'count': 2}},
...
{'d': {'value': 3, 'count': 2}},
]
CodePudding user response:
One way to do this:
from django.db.models import Count
fields = ["a", "b", "c", "d"]
field_most_frequent_mapping = {}
for field in fields:
field_most_frequent_mapping[field] = (
myTable.objects.values(field)
.annotate(field_count=Count(field))
.order_by("-field_count")[0][field]
)
You'll get a dictionary instead of a tuple, but you can work out the rest ;)