2

I think many similar questions have already been asked, but I could not make up a copy-paste solution. So here is the problem:

I periodically compute analysis on my user data for reporting. I made up a model to store these computations in my sqlite database. 

class report_data(models.Model):
    name = models.TextField()
    matrix = PickledObjectField()
    creation_date = models.DateTimeField('date of creation')

I am using an append only scheme, to persist historical data, so there are many records in database, that have

  • same name
  • different matrix
  • different creation date

My report view now needs a set, which contains that records with most recent creation date and unique names. Unfortunately sqlite does not support DISTINCT ON and the solution I found here, does not make sure, that it is always the most recent matrix, does it?

report_data.objects.all().distinct()

Does not work either, because, these records are all distinct by there key. As well as 

report_data.objects.all().latest("creation_date")

does not work, because it does only return the one most recent element without taking into account the name column.

I am quite a beginner in django, in SQL I would try something like

SELECT max(date),name,matrix FROM report_data group by name

but I have not tested this.

EDIT:

By support of the comment discussion I made up some kind of solution, which looks like this:

a = report_data.objects.values('name').annotate(latest=Max('creation_date'))

Then, I got all the right creation dates, but how to get the annotated objects?

EDIT2:

Now I use a ReportManager, that looks like this:

class ReportManager(models.Manager):
    def recent(self):
        a = report_data.objects.values("name").annotate(latest_id=Max('id'))
        a = list(v['latest_id'] for v in a)
        return report_data.objects.filter(id__in=a).order_by('-creation_date')

But I am not quite happy with this solution. Thanks for posting a real solution!

Community
  • 1
  • 1
Milla Well
  • 3,193
  • 3
  • 35
  • 50

1 Answers1

0

Are you looking for Max?

from django.db.models import Max # also available: Q, Count, Min, Sum, Avg, StdDev, Variance, ...
report_data.objects.all().aggregate(Max('creation_date'))
Thomas Schwärzl
  • 9,518
  • 6
  • 43
  • 69
  • This is just great! It is almost, what I am searching for. This returns the value of the maximum, not the object itself. this is also discussed here: http://stackoverflow.com/questions/844591/how-to-do-a-select-max-in-django , but is `a = report_data.objects.all().aggregate(Max('creation_date'));result = report_data.objects.get(creation_date = a)` a good solution? I think it is not made sure, that it always returns the same.. – Milla Well Jan 08 '13 at 11:05