Commits

Kyle Fuller committed f5aeb3e

forum: Use annotate instead of use raw SQL

Comments (0)

Files changed (1)

lithium/forum/managers.py

 from django.db import connection, models, transaction
+from django.db.models import Count, Max
 
 # from lithium.forum.models import Thread, Post
 
 class ForumManager(models.Manager):
     def get_query_set(self):
-        qs = super(ForumManager, self).get_query_set()
-        
-        thread_count_query = 'SELECT COUNT(*) FROM %(thread_table)s WHERE %(thread_table)s.%(thread_forum)s = %(forum_table)s.%(forum_id)s' % {
-            # 'thread_table': Thread._meta.db_table,
-            # 'thread_forum': Thread._meta.get_field('forum').column,
-            'thread_table': 'forum_thread',
-            'thread_forum': 'forum_id',
-            'forum_table': self.model._meta.db_table,
-            'forum_id': self.model._meta.get_field('id').column
-        }
-        
-        return qs.extra(select={
-            'thread_count': thread_count_query,
-        })
+        return super(ForumManager, self).get_query_set().annotate(
+                thread_count=Count('thread'))
     
     def update_position(self, position, addition=1, increment=True):
         if isinstance(position, (list, tuple)):
 
 class ThreadManager(models.Manager):
     def get_query_set(self):
-        qs = super(ThreadManager, self).get_query_set()
-        
-        post_count_query = 'SELECT COUNT(*) FROM %(post_table)s WHERE %(post_table)s.%(post_thread)s = %(thread_table)s.%(thread_id)s' % {
-            # 'post_table': Post._meta.db_table,
-            # 'post_thread': Post._meta.get_field('thread').column,
-            'post_table': 'forum_post',
-            'post_thread': 'thread_id',
-            'thread_table': self.model._meta.db_table,
-            'thread_id': self.model._meta.get_field('id').column
-        }
-        
-        last_post_time_query = 'SELECT %(post_table)s.%(post_pub_date)s FROM %(post_table)s WHERE %(post_table)s.%(post_thread)s = %(thread_table)s.%(thread_id)s ORDER BY %(post_table)s.%(post_pub_date)s DESC LIMIT 1' % {
-            'post_table': 'forum_post',
-            'post_thread': 'thread_id',
-            'post_pub_date': 'pub_date',
-            'thread_table': self.model._meta.db_table,
-            'thread_id': self.model._meta.get_field('id').column
-        }
-        
-        return qs.extra(select={
-            'post_count': post_count_query,
-            'last_post_date': last_post_time_query,
-        }, order_by=['-is_sticky', '-last_post_date'])
+        return super(ThreadManager, self).get_query_set().annotate(
+            post_count=Count('post'),
+            last_post_date=Max('post__pub_date')
+        ).order_by('-is_sticky', '-last_post_date')
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.