Django: select values with max timestamps or join to the same table

Question

I have a simple Django models

class Server(models.Model):
    name = models.CharField(max_length=120)

class ServerPropertie(models.Model):
    name = models.CharField(max_length=120)
    value = models.CharField(max_length=120)
    timestamp = models.DateTimeField()
    server = models.ForeignKey(Server)

I want to add get_properties method to the Server model, which will return all last properties for current server. I mean it should return name and value of all properties names for current server and each uniq propertie name should have a value, which row has a maximum timestamp.

I can do it in raw hardcoded raw SQL(i use postgres):

SELECT t1.name, t1.value FROM environments_serverpropertie t1
JOIN (SELECT max("timestamp") "timestamp", name 
      FROM environments_serverpropertie
      group by name) t2 on t1.name = t2.name and t1.timestamp = t2.timestamp;

or in in python, but i believe that there exists pythonic solution. Could you please help me.

Answer 1

if you're using PostgreSQL, usual syntax for that is:

select distinct on (name)
    name, value
from environments_serverpropertie
where server = ...
order by name, timestamp desc

From PostgreSQL documentation:

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

You can see and try it in sql fiddle demo.

It's possible to translate this syntax to django, from django documentation:

On PostgreSQL only, you can pass positional arguments (*fields) in order to specify the names of fields to which the DISTINCT should apply. This translates to a SELECT DISTINCT ON SQL query.

So in django it will be something like:

ServerPropertie.objects.filter(...).order_by('name', '-timestamp').distinct('name')