display vote counts (badly)

3 files changed, 13 insertions, 3 deletions

diff --git a/apioforum/db.py b/apioforum/db.py
index 1faa167..67989df 100644
--- a/apioforum/db.py
+++ b/apioforum/db.py
@@ -109,7 +109,10 @@ CREATE TABLE votes (
 );
 ALTER TABLE posts ADD COLUMN vote INTEGER REFERENCES votes(id);
 """,
-
+"""
+CREATE VIEW vote_counts AS
+    SELECT poll, option_idx, count(*) AS num FROM votes WHERE current GROUP BY option_idx,poll; 
+""",
     
 ]
 
diff --git a/apioforum/templates/view_thread.html b/apioforum/templates/view_thread.html
index 513ee59..622cf06 100644
--- a/apioforum/templates/view_thread.html
+++ b/apioforum/templates/view_thread.html
@@ -9,7 +9,7 @@
 <p>{{poll.title}}</p>
 <ul>
     {%for opt in poll.options%}
-    <li>#{{opt.option_idx}} - {{opt.text}}</li>
+    <li>#{{opt.option_idx}} - {{opt.text}} - {{opt.num or 0}}</li>
     {%endfor%}
 </ul>
 {% endif %}
diff --git a/apioforum/thread.py b/apioforum/thread.py
index 7d068c4..ec50b3b 100644
--- a/apioforum/thread.py
+++ b/apioforum/thread.py
@@ -29,9 +29,16 @@ def view_thread(thread_id):
             WHERE thread_tags.thread = ?
             ORDER BY tags.id""",(thread_id,)).fetchall()
         poll = None
+        votes = None
         if thread['poll'] is not None:
             poll_row = db.execute("SELECT * FROM polls where id = ?",(thread['poll'],)).fetchone()
-            options = db.execute("SELECT * FROM poll_options WHERE poll = ?",(poll_row['id'],)).fetchall()
+            options = db.execute("""
+                SELECT poll_options.*, vote_counts.num
+                FROM poll_options
+                LEFT OUTER JOIN vote_counts  ON poll_options.poll = vote_counts.poll
+                                            AND poll_options.option_idx = vote_counts.option_idx 
+                WHERE poll_options.poll = ?;
+                """,(poll_row['id'],)).fetchall()
             poll = {}
             poll.update(poll_row)
             poll['options'] = options