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.
$ ./manage.py debugsqlshell
>>> Version.objects.distinct('parent').order_by('parent_id', '-created_at')
SELECT DISTINCT ON ("content_version"."parent_id") "content_version"."id",
"content_version"."parent_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",
"content_version"."parent_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.
Args:
owner (User): An owner of Content object.
Return:
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': owner.id}
with connection.cursor() as c:
c.execute(query % context)
bytes_used = c.fetchone()
return bytes_used[0] or 0
Happy coding.
Han.Mir - Step Forward 