Relative step by step ID reference field

Question

I have a table for storing reservations for specific events; The corresponding part:

class Reservation(models.Model): # django creates an auto-increment field "id" by default event = models.ForeignKey(Event) # Some other reservation-specific fields.. first_name = models.CharField(max_length=255) 

Now I want to get the sequential identifier of this reservation relative to the reservation for the same event.

Disclaimer: Of course, we assume that reservations are never deleted or their relative position may change.

Example:

 +----+-------+------------+--------+ | ID | Event | First name | Rel.ID | +----+-------+------------+--------+ | 1 | 1 | AAA | 1 | | 2 | 1 | BBB | 2 | | 3 | 2 | CCC | 1 | | 4 | 2 | DDD | 2 | | 5 | 1 | EEE | 3 | | 6 | 3 | FFF | 1 | | 7 | 1 | GGG | 4 | | 8 | 1 | HHH | 5 | +----+-------+------------+--------+ 

The last column is the "Relative identifier", that is, a sequential number without spaces for all reservations of the same event.

Now, what is the best way to do this without having to manually calculate the relative identifier for each import (I don't like this)? I use postgresql as the base database, but I would prefer to stick with the django abstraction layer to preserve this migration (i.e., No database-specific solutions like triggers, etc.).

Answer 1

I hate always being the one who answers my questions, but I decided to use this:

 class Reservation(models.Model): # ... def relative_id(self): return self.id - Reservation.objects.filter(id__lt=self.id).filter(~Q(event=self.event)).all().count() 

Assuming that records from a reservation are never deleted, we can safely assume that the "relative identifier" is an incremental id - (the amount of reservation before it does not belong to a single event).

I think of any flaws, but I have not found.

Answer 2

Filtering using Reservation.objects.filter(event_id = some_event_id) should be sufficient. This will give you a QuerySet, which should have the same order every time. Or am I missing something in your question?