CODEWITHSUNDEEP
sqlruby-on-railsdatabaserelational-database
Gaurav
Gaurav

Reputation: 121

Sorting posts based on votes in a relational database?

I have two database tables. posts and votes.

**posts table;**
id | user_id |        content         
 1 |       1 | first post   
 2 |       1 | second post  
 3 |       1 | third                 

**votes table;**

 vote | post_id  
------+---------
 t    |       1
 t    |       2
 t    |       2
 t    |       2
 f    |       2
 t    |       3
 t    |       3
 t    |       3
vote is a boolean with values true or false

Now I want to find all the posts sorted in descending order such that the result reflects the votes (number of upvotes (true vote) - number of downvotes (false vote)).

SELECT  posts.*, count(votes.vote is true) -  count(votes.vote is false) as vote_count
FROM "posts"
LEFT JOIN votes AS votes ON votes.post_id = posts.id
GROUP BY posts.id 
ORDER BY vote_count DESC LIMIT 100;

The above query returns the result but all the values in vote_count field are 0(zero). (I know I am a NOOB!)

What will be the correct query in this scenario?

The desired result is (values inside '()' is for reference purpose only):

 id | votes | content        
----+-----------------------
  3 | 3(3-0)| third 
  2 | 2(3-1)| second post
  1 | 1(1-0)| first post

Thanks.

P.S. - Will help a lot if you can give me corresponding rails query too.

Upvotes: 0

Views: 59

Answers (1)

SMA
SMA

Reputation: 37033

Try Inner join with CASE WHEN THEN as below:

SELECT SUM(CASE WHEN vote = 't' then 1 ELSE -1 END) AS Votes, post_id, content
FROM votes v INNER JOIN posts p ON p.id = v.post_id
GROUP BY post_id
ORDER BY SUM(CASE WHEN vote = 't' then 1 ELSE -1 END) DESC

Upvotes: 3

Related Questions