Django auto increment fields-CodePudding

I have 2 columns named Serial and Bag I need them to be auto incremented but based on each other and also based on the user that will update the record, so every bag should have 100 serial and reset the number automatically after reaching 100, then start again with Bag number 2 and put 100 serial in it and reset.

For example:

when user update the first record the Bag will start with number 1 and Serial will be also number 1 the second record Bag will still number 1 and the serial will be changed to number 2 till reach 100 Serial in one Bag, then we will start again with bag number 2 and serial number 1 etc ...

Thanks

CodePudding user response：

The way you explain your example is a bit confusing but I'll try to give you an answer.

I assume the "2 columns named Serial and Bag" are fields of the same model and as you replied in the comments "the record is already existing but it has empty serial and bag", which means the auto-increment begins when the record is updated. Lastly, you mentioned first and second records implying that there are multiple records in this model. Based on these criteria, what you can do is add a save method in your model:

# Sample model
class Record(models.Model):
  bag = models.IntegerField(default=0, null=True)
  serial = models.IntegerField(default=0, null=True)
  created_at = models.DateTimeField(auto_now=True, null=True)

  def save(self, *args, **kwargs):
    # Ensures the record will only auto-increment during update
    if self.created_at:
      # Retrieves the object with the highest bag & serial value
      latest_record = Record.objects.all().order_by('bag', 'serial').last()

      # Incrementing logic
      if latest_record.serial_no   1 <= 100:
        self.bag = latest_record.bag if latest_record.bag > 0 else 1
        self.serial = latest_record.serial   1
      else:
        self.bag = latest_record.bag   1
        self.serial = 1

    super(Record, self).save(*args, **kwargs)

Now, each time you write save like:

record = Record()
record.save()

The model save method executes.

CodePudding user response：

Rather than do the incrementing logic in python, where it is subject to race conditions if multiple updates can happen concurrently, it should be possible to push it down into the database.

Something like:


update foop set
  bag=vala,
  ser=valb 
from (
    select 
       case when ser >= 5 then bag 1 else bag end as vala,
       case when ser >= 5 then 1 else ser 1 end as valb 
    from foop
    order by bag desc nulls last,
       ser desc nulls last
    limit 1) as tt
where some_primarykey = %;

It might be possible to translate that into django ORM, but it might also be easier and more readable to just drop into raw SQL or sneak it in via .extra() on a queryset than attempt to shoehorn it in.