Getting the last sub entries per entry

Now, I am trying to get the sizes of the last created versions of each contents.

Here’s the simple model diagram.


It looks just like a blog post and its comments. Here’s sample data.

Content model

id name
1 My Content 1
2 My Content 2

Version model

id parent_id size
1 1 100
2 1 200
3 1 300
4 2 400
5 2 500

The records with grayed background would be the result set as following table.

Expected result set (Versions only)

id parent_id size
3 1 300
5 2 500


My first thought was using group by with Max() function which requires a join or sub query. The 2nd thought was using distinct, but I wasn’t sure that grabbing the last ones only per parent. Anyway, here are the django orm and the sql queryset generated.
I tested with django-debug-toolbar’s debugsqlshell command which shows sql query strings per command with execution times. Below, I discard all execution times from the result because the result may vary based on the performance of the machine tested with.

$ ./ debugsqlshell

>>> Version.objects.distinct('parent').order_by('parent_id', '-created_at')
SELECT DISTINCT ON ("content_version"."parent_id") "content_version"."id",
FROM "content_version"
ORDER BY "content_version"."parent_id" ASC,
         "content_version"."created_at" DESC

Now I need to get size of each versions.

>>> Version.objects.filter(parent__owner=owner).distinct('parent').order_by('parent_id', '-created_at')
SELECT DISTINCT ON ("content_version"."parent_id") "content_version"."id",
FROM "content_version"
INNER JOIN "content_content" ON ("content_version"."parent_id" = "content_content"."id")
WHERE ("content_version"."is_archived" = false
       AND "content_content"."is_archived" = false
       AND "content_content"."owner_id" = '65ed9958-788e-11e6-9b70-a820663cfc41'::uuid)
ORDER BY "content_version"."parent_id" ASC,
         "content_version"."created_at" DESC

Now I need to add a filter to getting none-archived records and owned by a user.

>>> Version.objects.filter(parent__owner=owner).distinct('parent').order_by('parent_id', '-created_at').values_list("size", flat=True)
SELECT DISTINCT ON ("content_version"."parent_id") "content_version"."size"
FROM "content_version"
INNER JOIN "content_content" ON ("content_version"."parent_id" = "content_content"."id")
WHERE ("content_version"."is_archived" = false
       AND "content_content"."is_archived" = false
       AND "content_content"."owner_id" = '65ed9958-788e-11e6-9b70-a820663cfc41'::uuid)
ORDER BY "content_version"."parent_id" ASC,
         "content_version"."created_at" DESC

So far so good. 🙂
Oh, Now I want to get the total size of all the last versions.
I tried to add aggregation to get Sum() of size, and the django's orm results broken queries. If anyone knows how to get the result with django's orm, please let me know. I may buy you a cup of coffee or beer sometime.

Anyway, so I decided to use raw sql for the result and I didn't want to use python's sum() function because the number of records was arbitrary and may get lots of records that would make the application slow down.

Here's the last code snippet I used.

def get_storage_bytes_used(owner):
    Returns total bytes used for user.
    The calculation is Sum of latest versions of each `Content` records.

        owner (User): An owner of Content object.

        bytes_used (int): Total bytes used for a given user.
    query = '''SELECT sum(size) from (
        SELECT DISTINCT ON (v."parent_id") v."size"
        FROM "content_version" AS v
        INNER JOIN "content_content" AS c ON (v."parent_id" = c."id")
        WHERE (v."is_archived" = false
            AND c."is_archived" = false
            AND c."owner_id" = '%(owner_id)s'::uuid)
        ORDER BY v."parent_id" ASC, v."updated_at" DESC) as t'''
    context = {'owner_id':}

    with connection.cursor() as c:
        c.execute(query % context)
        bytes_used = c.fetchone()

    return bytes_used[0] or 0

Happy coding.


Django @property executes extra database calls? Ensure using cached result instead.

People made mistakes as below code that executes database call every time it’s invoked.

class MyModel(models.Model):
    def is_ready(self):
        status = self.check_status_from_db()
        return True if status else None

Assuming that there’re 2 or more {{ object.is_ready }} in your template especially in a loop on a list page. See what would happen via django debug toolbar.

Let’s fix the issue with the below code.

class MyModel(models.Model):
    def is_ready(self):
        if not hasattr(self, '_is_ready'):
            status = self.check_status_from_db()
            self._is_ready = True if status else None
        return self._is_ready

Now, retest the code and see what’s difference.

Django ETag doesn’t work on my local machine. Why?

Django Etag doesn’t work on my local machine via the virtual webserver.

The reason is that browser sends a request via HTTP/1.1 and django sends a response via HTTP/1.0.

How I can update the version on my machine? Here’s a monkey patch that tweak the version number.

Do not use it on your web server if you use a web webserver such as apache or nginx.


# HACK: without HTTP/1.1, Chrome ignores certain cache headers during development!
#       see for a bit more discussion.
from wsgiref import simple_server
simple_server.ServerHandler.http_version = "1.1"

I found this monkey patch from but unfortunately I lost the link. 😦

Inheritance Model, Polymorphism in Django

First of all, you need to install django-model-utils which allows you to create inheritance models.

ref. or

sudo pip install django-model-utils

Next, create your model.

from django.db import models
from model_utils.models import InheritanceCastModel

class Content(InheritanceCastModel):
    title = models.CharField(max_length=50)

class Video(Content):
    autoPlay = models.BooleanField(default=True)

class Audio(Content):
    Lyric = models.CharField(max_length=2000, null=True)


contents = Content.objects.all()
for content in contents:
    if hasattr(content, 'audio'):
        print 'This is audio'
        print 'This is video'

Also, you can add a filter as below.

list = Content.objects.filter(real_type__name = 'audio')

Rename Model(Table) in Django South Migration

When you change the name of model and create a migration file, you will see the code like “delete table, create table, and create relationship”.
It means you will lose all data in the previous model. However, here’s a simple way to rename model(table) with data.
Change the migration file as below or you may create yours.

class Migration(SchemaMigration):

    def forwards(self, orm):
        # Renaming model 'Tag' to 'RemarkTag'

    def backwards(self, orm):
        # Renaming model 'Tag' to 'RemarkTag'

Django says “Unable to open” SQLite3

First of all, make sure the db path in your settings file.

import os
PROJECT_ROOT = os.path.dirname(os.path.abspath(__file__))
    'default': {
        'ENGINE': 'django.db.backends.sqlite3', # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.
        'NAME': os.path.join(PROJECT_ROOT, 'talktrack.sqlite'), # Or path to database file if using sqlite3.
        'USER': '',                      # Not used with sqlite3.
        'PASSWORD': '',                  # Not used with sqlite3.
        'HOST': '',                      # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '',                      # Set to empty string for default. Not used with sqlite3.

Then, check permissions below file and folders. (django needs full permissions)
1. db file.
i.e. yourapp.sqlite
2. /var/tmp
3. parent folder of db file
i.e. /var/www/yourapp
if the db file is in the folder. /var/www/yourapp/yourapp.sqlite

sudo chown -R user:group file-or-folder