Order queryset by JSON field of latest FK

Question

I have such models:

class Configuration(models.Model):
    @property
    def latest_value_A(self):
        return self.result_set.latest("created_time").data.get("value_A")

class Result(models.Model):
    data = models.JSONField()  # "value_A" key is present in JSON
    configuration = models.ForeignKey(Configuration, on_delete=models.CASCADE)
    created_time = models.DateTimeField()

I want to do something like Configuration.objects.order_by("latest_value_A") which obviously fails cause it's just a property and not stored in DB. But what would be the proper way of doing it?

UPDATE: I'm using PostgreSQL

Do you use a PostgreSQL database? – Willem Van Onsem Mar 01 '21 at 20:34 — Willem Van Onsem, Mar 01 '21 at 20:34
Yep, I use PostgreSQL – Jakub Nowakowski Mar 01 '21 at 20:53 — Jakub Nowakowski, Mar 01 '21 at 20:53

score 0 · Accepted Answer · answered Mar 01 '21 at 21:07

0

You can use the sorted function from python. Something like:

sorted(Configuration.objects.all(), key=lambda t: t.latest_value_A)

A reference if needed: Ordering Django queryset by a @property

answered Mar 01 '21 at 21:07

Patrick Freitas

681
1
5
18

Nice solution! Solved my problem easily, I'm wondering on it's efficiency, tho. – Jakub Nowakowski Mar 01 '21 at 23:56

Order queryset by JSON field of latest FK

1 Answers1