Aggregation and extra values with Django

Question

Hi, I have a model which looks like this:

class MyModel(models.Model)
    value = models.DecimalField()
    date = models.DatetimeField()

I'm doing this request:

MyModel.objects.aggregate(Min("value"))

and I'm getting the expected result:

{"mymodel__min": the_actual_minimum_value}

However, I can't figure out a way to get at the same time the minimum value AND the associated date (the date at which the minimum value occured).

Does the Django ORM allow this, or do I have to use raw SQL ?

Answer 1

What you want to do is annotate the query, so that you get back your usual results but also have some data added to the result. So:

MyModel.objects.annotate(Min("value"))

Will return the normal result with mymodel__min as an additional value

In reply to your comment, I think this is what you are looking for? This will return the dates with their corresponding Min values.

MyModel.objects.values('date').annotate(Min("value"))

Edit: In further reply to your comment in that you want the lowest valued entry but also want the additional date field within your result, you could do something like so:

MyModel.objects.values('date').annotate(min_value=Min('value')).order_by('min_value')[0] 

This will get the resulting dict you are asking for by ordering the results and then simply taking the first index which will always be the lowest value. See more