(No Ratings Yet)
Loading...

My objective was to create (in an automated, easy way for anyone deploying the project) a database view, for which I could have a regular django model, that I could use like any other model backed up by database tables. Turns out the easiest way is to create a custom migration. In my example I was using postgresql, it could be you require doing some minor changes for mysql.

Model

Let’s say we have a wood production plant, and we want some daily statistics, but all we have is a model OutgoingPackage in package named packages. That means, that the underlying database table will be named packages_outgoingpackage.

class OutgoingPackage(models.Model):
    timestampFinished = models.DateTimeField(null=True, db_column='timestampfinished')
    numBoards = models.IntegerField(default=0, db_column='numboards')
    lamellaThickness = models.FloatField(default=0.0, db_column='lamellathickness')
    lamellaLength = models.IntegerField(default=0.0, db_column='lamellalength')
    lamellaWidth = models.FloatField(default=0.0, db_column='lamellawidth')

Database view

The idea is to create a view, where we could group the number of meters (length * number of boards) made for a specific dimension (equal height & width) on a specific date. We create a custom migration that we put in the migrations folder.

class Migration(migrations.Migration):
 
    dependencies = [
        ('packages', '0037_something'),
    ]
 
    sql = """
        create VIEW packages_outgoingpackagestatistic as
            SELECT row_number() OVER () AS id, 
                    date(timestampfinished) as date, 
                    lamellathickness, 
                    lamellawidth,
                    SUM(lamellalength*numboards) as lm
            FROM packages_outgoingpackage 
            GROUP BY date, 
                        lamellathickness, 
                        lamellawidth;
    """
 
    operations = [
        migrations.RunSQL("drop view if exists packages_outgoingpackagestatistic;"),
        migrations.RunSQL(sql)
    ]

With this, we can now create a regular model, that we mark as non-managed by django. Creating this model and running makemigrations will create a migration file for it, but it will not actually create the database tables but it will use the view we made with the previous migration. Note we also add id field, so that django admin doesn’t throw any errors becacuse it expects it.

Model based on database view

class OutgoingPackageStatistic(models.Model):
    date = models.DateField(default=timezone.now)
    lamellathickness = models.FloatField(default=0.0)
    lamellawidth = models.FloatField(default=0.0)
    lm = models.FloatField(default=0.0)
 
    class Meta:
        managed = False
        db_table = 'packages_outgoingpackagestatistic'

After running migrate, we can now add this model to our django admin, create django rest framework views, use django commands to filter it like we would any other model.

Note that this is probably not something a beginner should get into. By beginner I mean someone that doesn’t particularly understand how django migrations and the underlying database schema work, because fixing a messed up schema on a database you can’t just drop and create again will be a pain in the a**. 🙂

Your email is kept private. Required fields are marked *