3

Take these models:

class Rocket(Model):
   ...

class Flight(Model):
   rocket = ForeignKey(Rocket)
   start_time = DateTimeField(...)

If I want to get start times of the latest flight for every rocket, that is simple:

>>> Flight.objects.values('rocket').annotate(max_start_time=Max('start_time'))
<QuerySet [
    {'rocket': 3, 'max_start_time': datetime.datetime(2019, 6, 13, 6, 58, 46, 299013, tzinfo=<UTC>)},
    {'rocket': 4, 'max_start_time': datetime.datetime(2019, 6, 13, 6, 59, 12, 759964, tzinfo=<UTC>)},
    ...]>

But what if instead of max_start_time I wanted to select IDs of those same Flights?

In other words, I want to get the ID of the latest Flight for every rocket.

frnhr
  • 12,354
  • 9
  • 63
  • 90
  • i'm sorry if i'm missing something here, but when you say "IDs of those same Flights" you mean "3" and "4" in the example you gave right? – xplo4d Sep 06 '19 at 16:31
  • 1
    @xplo4d Those would be IDs of Rockets. I'm interested in IDs of Flights that have those `max_start_time` values in the example. – frnhr Sep 06 '19 at 16:34
  • Of course, without doing an additional query. There is no guarantee that `start_time` values are unique. – frnhr Sep 06 '19 at 16:34

1 Answers1

0

What database backend are you using? If your database backend has support for DISTINCT ON this is most easily accomplished by:

Flight.objects.order_by("rocket", "-start_time").distinct("rocket").values("id", "rocket")

azundo
  • 5,902
  • 1
  • 14
  • 21