MySQL: Ordering By Using Two Counts From Another Table?

Question

I have one sql table that looks like this called "posts":

id | user
--------------------------------
0  | tim
1  | tim
2  | bob

And another called "votes" that stores either upvotes or downvotes on the posts in the "posts" table:

id | postID | type
--------------------------------
0  | 0      | 0
1  | 2      | 1
2  | 0      | 1
3  | 0      | 1
4  | 3      | 0

In this table, the 'type' is either a 0 for downvote or 1 for upvote.

How would I go about ordering posts by "tim" by the number of (upvotes - downvotes) the post has?

Answer 1

SELECT
  p.id,
  p.user,
  SUM(v.type * 2 - 1) AS votecount
FROM posts p
  LEFT JOIN votes v ON p.id = v.postID
WHERE p.user = 'tim'
GROUP BY p.id, p.user
ORDER BY votes DESC

---

UPDATE – p and v explained.

In this query, p and v are aliases of, respectively, posts and votes. An alias is essentially an alternative name and it is defined only within the scope of the statement that declares it (in this case, the SELECT statement). Not only a table can have an alias, but a column too. In this query, votecount is an alias of the column represented by the SUM(v.type * 2 - 1) expression. But presently we are talking only about tables.

Before I go on with explanation about table aliases, I'll briefly explain why you may need to prefix column names with table names, like posts.id as opposed to just id. Basically, when a query references more than one table, like in this case, you may find it quite useful always to prefix column names with the respective table names. That way, when you are revisiting an old script, you can always tell which column belongs to which table without having to look up the structures of the tables referenced. Also it is mandatory to include the table reference when omitting it creates ambiguity as to which table the column belongs to. (In this case, referencing the id column without referencing the posts table does create ambiguous situation, because each table has got their own id.)

Now, a large and complex query may be difficult to read when you write out complete table names before column names. This is where (short) aliases come in handy: they make a query easier to read and understand, although I've already learnt that not all people share that opinion, and so you should judge for yourself: this question contains two versions of the same query, one with long-named table references and the other with short-aliased ones, as well as an opinion (in a comment to one of the answers) why aliases are not suitable.

Anyway, using short table aliases in this particular query may not be as beneficial as in some more complex statements. It's just that I'm used to aliasing tables whenever the query references more than one.

This MySQL documentation article contains the official syntax for aliasing tables in MySQL (which is actually the same as in standard SQL).

Answer 2

Not tested, but should work:

select post.id, sum(if(type = 0, -1, 1)) as score
from posts join votes on post.id = votes.postID
where user = 'tim'
group by post.id
order by score

Do you plan to concur SO? ;-)