I am not well in django orm advance query.
problem is:
select rows with max a column value distinct by other column and order by date (desc)
I found answer on stackoverflow see this
Answer in MYSQL is this (from above give url)
SELECT tt.*
FROM topten tt
INNER JOIN
(SELECT home, MAX(datetime) AS MaxDateTime
FROM topten
GROUP BY home) groupedtt
ON tt.home = groupedtt.home
AND tt.datetime = groupedtt.MaxDateTime
Its Equivalent Django ORM query ..
My Model is
class ModelStatus(models.Model):
name = models.CharField(max_length=100, default='---')
status = models.CharField(max_length=10, default='---')
fall = models.CharField(max_length=100, default='---')
rise = models.CharField(max_length=100, default='---')
add_date = models.DateTimeField(auto_now_add=False, auto_now=True)
error_message = models.TextField(default=' ')
to = models.ForeignKey('model1',
related_name="status",
related_query_name="hstatus")
distinct column: name
max value column: add_date
EXAMPLE
id name add_date status fall_count | rise_count
---|-----|------------|--------|-----------|--------
1 | ab | 04/03/2009 | up | 399 | 100
2 | aa | 04/03/2009 | down | 244 | 200
3 | aa | 03/03/2009 | down | 555 | 210
4 | ba | 03/03/2009 | up | 300 | 256
5 | ab | 03/03/2009 | up | 200 | 145
OUTPUT
id name add_date status fall_count | rise_count
---|-----|------------|--------|-----------|--------
1 | ab | 04/03/2009 | up | 399 | 100
2 | aa | 04/03/2009 | down | 244 | 200
4 | ba | 03/03/2009 | up | 300 | 256
