diff options
| -rw-r--r-- | apioforum/db.py | 5 | ||||
| -rw-r--r-- | apioforum/templates/view_thread.html | 2 | ||||
| -rw-r--r-- | apioforum/thread.py | 9 |
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 |