summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorDan McGee <dan@archlinux.org>2012-11-20 15:57:05 -0600
committerDan McGee <dan@archlinux.org>2012-11-20 18:17:48 -0600
commit160a08bba5324b25abd9e866b884c91d75e597b0 (patch)
treef1e6dd7cd724a584682970fa43cfdcba0b54ca40
parent9e9157d0a8cbf9ea076231e438fb30f58bff8e29 (diff)
downloadarchweb-160a08bba5324b25abd9e866b884c91d75e597b0.tar.gz
archweb-160a08bba5324b25abd9e866b884c91d75e597b0.zip
Improve performance of todolists query
Use some standard SQL and split the query into two different parts to save a lot of unnecessary sorting and field retrieval at the database level. The `CASE WHEN complete THEN 1 ELSE 0 END` syntax should be accepted by any database that implements proper SQL; it was tested in PostgreSQL and sqlite3 without issues. Signed-off-by: Dan McGee <dan@archlinux.org>
-rw-r--r--todolists/utils.py41
1 files changed, 26 insertions, 15 deletions
diff --git a/todolists/utils.py b/todolists/utils.py
index 94f39f71..03c47931 100644
--- a/todolists/utils.py
+++ b/todolists/utils.py
@@ -1,26 +1,37 @@
+from django.db import connections, router
from django.db.models import Count
-from main.models import Todolist
+from main.models import Todolist, TodolistPkg
-def get_annotated_todolists(incomplete_only=False):
- qs = Todolist.objects.all()
- lists = qs.select_related('creator').defer(
- 'creator__email', 'creator__password', 'creator__is_staff',
- 'creator__is_active', 'creator__is_superuser',
- 'creator__last_login', 'creator__date_joined').annotate(
- pkg_count=Count('todolistpkg')).order_by('-date_added')
- incomplete = qs.filter(todolistpkg__complete=False).annotate(
- Count('todolistpkg')).values_list('id', 'todolistpkg__count')
+def todo_counts():
+ sql = """
+SELECT list_id, count(*), sum(CASE WHEN complete THEN 1 ELSE 0 END)
+ FROM todolist_pkgs
+ GROUP BY list_id
+ """
+ database = router.db_for_write(TodolistPkg)
+ connection = connections[database]
+ cursor = connection.cursor()
+ cursor.execute(sql)
+ results = cursor.fetchall()
+ return {row[0]: (row[1], row[2]) for row in results}
- lookup = dict(incomplete)
- if incomplete_only:
- lists = lists.filter(id__in=lookup.keys())
+def get_annotated_todolists(incomplete_only=False):
+ lists = Todolist.objects.all().select_related(
+ 'creator').order_by('-date_added')
+ lookup = todo_counts()
- # tag each list with an incomplete package count
+ # tag each list with package counts
for todolist in lists:
- todolist.incomplete_count = lookup.get(todolist.id, 0)
+ counts = lookup.get(todolist.id, (0, 0))
+ todolist.pkg_count = counts[0]
+ todolist.complete_count = counts[1]
+ todolist.incomplete_count = counts[0] - counts[1]
+
+ if incomplete_only:
+ lists = [l for l in lists if l.incomplete_count > 0]
return lists