Autoincrement-like Field For Objects With The Same Foreign Key (django 1.8, Mysql 5.5)

I've got a Django model (let's call it ObjectLog), many of which are related to a single Object by a foreign key. See below for a brief definition: class ObjectLog(models.Model):

Solution 1:

I ended up solving the problem by constructing and executing a raw SQL query to do the autoincrement and insertion in a single database transaction. I spent a lot of time poring through the Django source code to understand how their default model save method works so that I could do this as robustly as possible. However, I fully expect that this will need to be modified for non-MySQL backends.

First, I created an abstract class which ObjectLog will now derive from, which features this new save method:

classAutoIncrementModel(models.Model):
    """
    An abstract class used as a base for classes which need the
    autoincrementing save method described below.
    """classMeta:
        abstract = Truedefsave(self, auto_field, auto_fk, *args, **kwargs):
        """
        Arguments:
            auto_field: name of field which acts as an autoincrement field.
            auto_fk:    name of ForeignKey to which the auto_field is relative.
        """# Do normal save if this is not an insert (i.e., the instance has a# primary key already).
        meta = self.__class__._meta
        pk_set = self._get_pk_val(meta) isnotNoneif pk_set:
            super(ObjectLog, self).save(*args, **kwargs)
            return# Otherwise, we'll generate some raw SQL to do the# insert and auto-increment.# Get model fields, except for primary key field.
        fields = meta.local_concrete_fields
        ifnot pk_set:
            fields = [f for f in fields ifnotisinstance(f, models.fields.AutoField)]

        # Setup for generating base SQL query for doing an INSERT.
        query = models.sql.InsertQuery(self.__class__._base_manager.model)
        query.insert_values(fields, objs=[self])
        compiler = query.get_compiler(using=self.__class__._base_manager.db)
        compiler.return_id = meta.has_auto_field andnot pk_set

        fk_name = meta.get_field(auto_fk).column
        with compiler.connection.cursor() as cursor:
            # Get base SQL query as string.for sql, params in compiler.as_sql():
                # compiler.as_sql() looks like:# INSERT INTO `table_objectlog` VALUES (%s,...,%s)# We modify this to do:# INSERT INTO `table_objectlog` SELECT %s,...,%s FROM# `table_objectlog` WHERE `object_id`=id# NOTE: it's unlikely that the following will generate# a functional database query for non-MySQL backends.# Replace VALUES (%s, %s, ..., %s) with# SELECT %s, %s, ..., %s
                sql = re.sub(r"VALUES \((.*)\)", r"SELECT \1", sql)

                # Add table to SELECT from and ForeignKey id corresponding to# our autoincrement field.
                sql += " FROM `{tbl_name}` WHERE `{fk_name}`={fk_id}".format(
                    tbl_name=meta.db_table,
                    fk_name=fk_name,
                    fk_id=getattr(self, fk_name)
                    )

                # Get index corresponding to auto_field.
                af_idx = [f.name for f in fields].index(auto_field)
                # Put this directly in the SQL. If we use parameter# substitution with cursor.execute, it gets quoted# as a literal, which causes the SQL command to fail.# We shouldn't have issues with SQL injection because# auto_field should never be a user-defined parameter.del params[af_idx]
                sql = re.sub(r"((%s, ){{{0}}})%s".format(af_idx),
                r"\1IFNULL(MAX({af}),0)+1", sql, 1).format(af=auto_field)

                # IFNULL(MAX({af}),0)+1 is the autoincrement SQL command,# {af} is substituted as the column name.# Execute SQL command.
                cursor.execute(sql, params)

            # Get primary key from database and set it in memory.if compiler.connection.features.can_return_id_from_insert:
                id = compiler.connection.ops.fetch_returned_insert_id(cursor)
            else:
                id = compiler.connection.ops.last_insert_id(cursor,
                    meta.db_table, meta.pk.column)
            self._set_pk_val(id)

            # Refresh object in memory in order to get auto_field value.
            self.refresh_from_db()

Then the ObjectLog model uses this like:

classObjectLog(AutoIncrementModel):
    classMeta:
        ordering = ['-created','-N']
        unique_together = ("object","N")
    object = models.ForeignKey(Object, null=False)                                                                                                                                                              
    created = models.DateTimeField(auto_now_add=True)
    issuer = models.ForeignKey(User)
    N = models.IntegerField(null=False)

    defsave(self, *args, **kwargs):
        # Set up to call save method of the base class (AutoIncrementModel)
        kwargs.update({'auto_field': 'N', 'auto_fk': 'event'})
        super(EventLog, self).save(*args, **kwargs)

This allows calls to ObjectLog.save() to still work as expected.