Django ORM how to get raw values grouped by a field

user_2723675 0
Viewed 1040

I have a model which is like so:

class CPUReading(models.Model):
    host = models.CharField(max_length=256)
    reading = models.IntegerField()
    created = models.DateTimeField(auto_now_add=True)

I am trying to get a result which looks like the following:

{
    "host 1": [
        {
            "created": DateTimeField(...),
            "value": 20
        },
        {
            "created": DateTimeField(...),
            "value": 40
        },
        ... 
    ],
    "host 2": [
        {
            "created": DateTimeField(...),
            "value": 19
        },
        {
            "created": DateTimeField(...),
            "value": 10
        },
        ... 
    ]
}

I need it grouped by host and ordered by created.

I have tried a bunch of stuff including using values() and annotate() in order to create a GROUP BY statement, but I think I must be missing something because in order to use GROUP BY it seems I need to use some aggregation function which I don't really want to do. I need the actual values of the reading field grouped by the host field and ordered by the created field.

This is more-or-less how any charting library needs the data.

I know I can make it happen with either python code or with raw sql queries, but I'd much prefer to use the django ORM, unless it explicitly disallows this sort of query.

python-3.x django-orm django python
3 Answers

As far as I'm aware, there's nothing in the ORM that makes this easy. If you want to do it in the ORM without raw queries, and if you're willing and able to change your data structure, you can solve this mostly in the ORM, with Python code kept to a minimum:

class Host(models.Model):
    pass

class CPUReading(models.Model):
    host = models.ForeignKey(Host, related_name="readings", on_delete=models.CASCADE)
    reading = models.IntegerField()
    created = models.DateTimeField(auto_now_add=True)

With this you can use two queries with fairly clean code:

from collections import defaultdict

results = defaultdict(list)
hosts = Host.objects.prefetch_related("readings")
for host in hosts:
    for reading in host.readings.all():
        results[host.id].append(
            {"created": reading.created, "value": reading.reading}
        )

Or you can do it a little more efficiently with one query and a single loop:

from collections import defaultdict

results = defaultdict(list)
readings = CPUReading.objects.select_related("host")
for reading in readings:
    results[reading.host.id].append(
        {"created": reading.created, "value": reading.reading}
    )
user_6259100

Assuming you are using PostgreSQL you can use a combination of array_agg and json_object to achieve what you're after.

from django.contrib.postgres.aggregation import ArrayAgg
from django.contrib.postgres.fields import ArrayField, JSONField
from django.db.models import CharField
from django.db.models.expressions import Func, Value

class JSONObject(Func):
    function = 'json_object'
    output_field = JSONField()

    def __init__(self, **fields):
        fields, expressions = zip(*fields.items())
        super().__init__(
            Value(fields, output_field=ArrayField(CharField())),
            Func(*expressions, template='array[%(expressions)s]'),
        )

readings = dict(CPUReading.objects.values_list(
    'host',
    ArrayAgg(
        JSONObject(
            created_at='created_at',
            value='value',
        ),
        ordering='created_at',
    ),      
))
user_701910

If you want to stay close to the Django ORM, you just need to remember this doesn't return a queryset but a dictionary and is evaluated on the fly, so don't use this in declarative scope. However, the interface is similar to QuerySet.values() and has the additional requirement that it needs to be sorted first.

class PlotQuerySet(models.QuerySet):
    def grouped_values(self, key_field, *fields, **expressions):
        if key_field not in fields:
            fields += (key_field,)
        values = self.values(*fields, **expressions)
        data = {}
        for key, gen in itertools.groupby(values, lambda x: x.pop(key_field)):
            data[key] = list(gen)

        return data


PlotManager = models.Manager.from_queryset(PlotQuerySet, class_name='PlotManager')

class CpuReading(models.Model):
    host = models.CharField(max_length=255)
    reading = models.IntegerField()
    created_at = models.DateTimeField(auto_now_add=True)
    objects = PlotManager()

Example:

CpuReading.objects.order_by(
    'host', 'created_at'
).grouped_values(
    'host', 'created_at', 'reading'
)                                                                                                  
Out[10]: 
{'a': [{'created_at': datetime.datetime(2020, 7, 13, 16, 45, 23, 215005, tzinfo=<UTC>),
   'reading': 0},
  {'created_at': datetime.datetime(2020, 7, 13, 16, 45, 23, 223080, tzinfo=<UTC>),
   'reading': 1},
  {'created_at': datetime.datetime(2020, 7, 13, 16, 45, 23, 230218, tzinfo=<UTC>),
   'reading': 2},
  ...],
 'b': [{'created_at': datetime.datetime(2020, 7, 13, 16, 45, 23, 241476, tzinfo=<UTC>),
   'reading': 0},
  {'created_at': datetime.datetime(2020, 7, 13, 16, 45, 23, 242015, tzinfo=<UTC>),
   'reading': 1},
  {'created_at': datetime.datetime(2020, 7, 13, 16, 45, 23, 242537, tzinfo=<UTC>),
   'reading': 2},
   ...]}
Admin1
Related