Counting number of positive and negative votes:

Question

I have the following tables:

post (id, title, content) etc
author (id, username) etc
author_vote (post_id, author_id, value)

Value is a tiny_int that can either be 1 or -1.

I want to count the number of positive and negative votes for each post:

$posts = sql_select($link, "SELECT post.*, author.username 
                            FROM post, author 
                            AND author.id = post.author_id");

Why does the following code not work?

array_walk($posts, function(&$post, $link){
   $post['positive'] = sql_select($link, "SELECT SUM(value) FROM author_vote WHERE post_id = $post['id']
                                            AND value  = 1");

   $post['negative'] = abs(sql_select($link, "SELECT SUM(value) FROM author_vote WHERE post_id = $post['id']
                                            AND value  = -1"));
});

I've also tried the following, this causes all the votes to be the same for each post:

foreach ($posts as &$post)
{
   $id = $post['id'];
   $post['positive'] = (int)sql_select($link, "SELECT SUM(value) FROM author_vote WHERE post_id = $id
                                           AND value  = 1");
   $post['negative'] = (int)abs(sql_select($link, "SELECT SUM(value) FROM author_vote WHERE post_id = $id
                                               AND value  = -1"));
}

Also is there any way to do this without having to query the database multiple times for each post? How would something that is constantly changing [like this] be (mem)cached?

What does `sql_select` return? Is there documentation for this function? — webbiedave, Apr 20 '11 at 22:17
In your current SQL you're using SUM when in fact you could use COUNT which would yield faster results. — Andre Backlund, Apr 20 '11 at 22:20
sql_select is a function I made myself, It throws an exception if an sql query is incorrect (i.e. it cannot perform the select) — jonnnnnnnnnie, Apr 20 '11 at 22:23

Thomas · Accepted Answer · 2011-04-20T23:14:06.963

You could do your count in a single query:

Select Sum( Case When value < 0 Then 1 Else 0 End ) As NegVotes
    , Sum( Case When value > 0 Then 1 Else 0 End ) As PosVotes
From author_vote
Where post_id = ...

If you want the positive and negative votes per post:

Select post_id
    , Sum( Case When value < 0 Then 1 Else 0 End ) As NegVotes
    , Sum( Case When value > 0 Then 1 Else 0 End ) As PosVotes
From author_vote
Group By post_id

If you wanted to combine your first query and second query you can get:

Select post....
    , author.username 
    , Coalesce(post_count.NegVotes,0) As NegVotes
    , Coalesce(post_count.PosVotes,0) As PosVotes
From post
    Join author
        On author.id = post.author_id
    Left Join   (
                Select post_id
                    , Sum( Case When value < 0 Then 1 Else 0 End ) As NegVotes
                    , Sum( Case When value > 0 Then 1 Else 0 End ) As PosVotes
                From author_vote
                Group By post_id
                ) As post_count
        On post_count.post_id = post.post_id

How would you combine this with the first query? So I can select all posts and votes associated with each post. — jonnnnnnnnnie, Apr 20 '11 at 23:09
@jonnnnnnnnnie - You would simply stuff this query into a derived table. I've expanded my answer to illustrate. — Thomas, Apr 20 '11 at 23:14

score 0 · Answer 2 · answered Apr 20 '11 at 22:24

I can't find the function sql_select() you are using, but you might be better off using count(*) in your SQL instead of trying to sum(). You just need to count rows it looks like, not sum the values. You could also get a little fancier and use a GROUP BY:

SELECT value, count(value) AS value_count FROM author_vote WHERE post_id = $id GROUP BY value

This will return one row for each unique value. Each row returned will report the unique value and the count of rows using that value.

Counting number of positive and negative votes:

2 Answers2