SQL SELECT from three tables with ordering in voting system

Question

I have a voting system in my laravel project I have each post have many answers and each answer have votes, and I try to show answers of a post with order of voting number. this the three tables:

1. posts: id, title, content...
2. answers: id, post_id, content...
3. answer_votes: id, post_id, answer_id, user_id, vote (1 or 0 or nothing if no one vote for the answer yet )

user_id in answer_votes tables is to prevent users to vote more than one time. I try to this:

select p.id,a.id,count(*) from posts p, answers a, answer_votes v
where  a.id=v.answer_id and p.id=a.post_id and p.id=1 
group by p.id,a.id;

select a.id from answers a, posts b
where a.post_id=1 
and  a.id in (select v.answer_id from answer_votes v where v.vote=1 group by v.answer_id )
or a.id not in (select v.answer_id from answer_votes v where v.answer_id=a.id and v.vote=1 group by v.answer_id );

but it's not working... please help me to resolve this problem.

Answer 1

Since you haven't provided any sample data, I created some of my own :

CREATE TABLE posts (id INT, title NVARCHAR(30), content NVARCHAR(200));
CREATE TABLE answers (id INT, post_id INT, content NVARCHAR(200));
CREATE TABLE answer_votes (id INT, post_id INT, answer_id INT, user_id INT, vote bit);

Sample data :

INSERT INTO posts VALUES(1, 'Favorite Color', 'Which is your favorite color?');
INSERT INTO posts VALUES(2, 'Favorite Cheese', 'Which is your favorite cheese?');

INSERT INTO answers VALUES(1, 1, 'Black');
INSERT INTO answers VALUES(2, 1, 'Red');
INSERT INTO answers VALUES(3, 1, 'Yellow');
INSERT INTO answers VALUES(4, 1, 'Green');
INSERT INTO answers VALUES(5, 1, 'Orange');

INSERT INTO answers VALUES(6, 2, 'Parmesan');
INSERT INTO answers VALUES(7, 2, 'Mozarella');
INSERT INTO answers VALUES(8, 2, 'Swiss Cheese');
INSERT INTO answers VALUES(8, 2, 'Colby-Jac');
INSERT INTO answers VALUES(9, 2, 'Monterey Jack');


INSERT INTO answer_votes VALUES(1, 1, 1, 100, 1);
INSERT INTO answer_votes VALUES(2, 1, 2, 101, 1);
INSERT INTO answer_votes VALUES(3, 1, 3, 102, 1);
INSERT INTO answer_votes VALUES(4, 1, 4, 103, 1);
INSERT INTO answer_votes VALUES(5, 1, 5, 104, 1);
INSERT INTO answer_votes VALUES(6, 1, 5, 105, 1);
INSERT INTO answer_votes VALUES(7, 1, 5, 106, 1);
INSERT INTO answer_votes VALUES(8, 1, 3, 107, 1);
INSERT INTO answer_votes VALUES(9, 1, 3, 108, 1);
INSERT INTO answer_votes VALUES(10, 1, 2, 109, 1);
INSERT INTO answer_votes VALUES(11, 1, 2, 110, 1);
INSERT INTO answer_votes VALUES(12, 1, 2, 111, 1);
INSERT INTO answer_votes VALUES(13, 1, 5, 112, 1);
INSERT INTO answer_votes VALUES(14, 1, 5, 113, 1);
INSERT INTO answer_votes VALUES(15, 1, 5, 114, 1);
INSERT INTO answer_votes VALUES(16, 1, 1, 115, 1);

INSERT INTO answer_votes VALUES(17, 2, 6, 100, 1);
INSERT INTO answer_votes VALUES(18, 2, 7, 101, 1);
INSERT INTO answer_votes VALUES(19, 2, 7, 102, 1);
INSERT INTO answer_votes VALUES(20, 2, 7, 103, 1);
INSERT INTO answer_votes VALUES(21, 2, 7, 104, 1);
INSERT INTO answer_votes VALUES(22, 2, 6, 105, 1);
INSERT INTO answer_votes VALUES(23, 2, 6, 106, 1);
INSERT INTO answer_votes VALUES(24, 2, 8, 107, 1);

INSERT INTO answer_votes VALUES(25, 2, 8, 108, 1);
INSERT INTO answer_votes VALUES(26, 2, 9, 109, 1);
INSERT INTO answer_votes VALUES(27, 2, 9, 110, 1);
INSERT INTO answer_votes VALUES(28, 2, 7, 111, 1);
INSERT INTO answer_votes VALUES(29, 2, 7, 112, 1);
INSERT INTO answer_votes VALUES(30, 2, 6, 113, 1);
INSERT INTO answer_votes VALUES(31, 2, 7, 114, 1);
INSERT INTO answer_votes VALUES(32, 2, 8, 115, 1);

You can use the following query :

SELECT      posts.id,
            posts.title,
            posts.content as Question,
            ans.content as Answer,
            COUNT(votes.answer_id) as TotalVotes
FROM        answers ans
INNER JOIN  posts posts on ans.post_id = posts.id
INNER JOIN  answer_votes votes on ans.id = votes.answer_id
WHERE       votes.vote = 1
GROUP BY    votes.answer_id,
            posts.id,
            posts.title,
            posts.content,
            ans.content
ORDER BY    posts.id,
            COUNT(votes.answer_id) DESC

You will see the following result set :

id      title                       Question                        Answer          TotalVotes
1     Favorite Color        Which is your favorite color?           Orange              6
1     Favorite Color        Which is your favorite color?           Red                 4
1     Favorite Color        Which is your favorite color?           Yellow              3
1     Favorite Color        Which is your favorite color?           Black               2
1     Favorite Color        Which is your favorite color?           Green               1
2     Favorite Cheese       Which is your favorite cheese?          Mozarella           7
2     Favorite Cheese       Which is your favorite cheese?          Parmesan            4
2     Favorite Cheese       Which is your favorite cheese?          Colby-Jac           3
2     Favorite Cheese       Which is your favorite cheese?          Swiss Cheese        3
2     Favorite Cheese       Which is your favorite cheese?          Monterey Jack       2

You can see this here -> http://sqlfiddle.com/#!9/357aef/1

NOTE : This will not include answers for which no one has voted yet.

EDIT : I understand that you want to include answers for which no one has voted yet. You can do that by slightly modifying the above query as follows :

SELECT      posts.id,
            posts.title,
            posts.content as Question,
            ans.content as Answer,
            COUNT(votes.answer_id) as TotalVotes

FROM        answers ans
LEFT JOIN   answer_votes votes ON ans.id = votes.answer_id
LEFT JOIN   posts posts ON ans.post_id = posts.id
GROUP BY    posts.id,
            posts.title,
            posts.content,
            ans.content,
            votes.answer_id
ORDER BY    posts.id,
            COUNT(votes.answer_id) DESC

Slightly modifying the above data set as follows :

INSERT INTO answer_votes VALUES(1, 1, 5, 100, 1)
INSERT INTO answer_votes VALUES(16, 1, 5, 115, 1)

Observe that now there are no votes for the answer "Black". This query would yield you the following result set:

id      title                   Question                    Answer      TotalVotes
1   Favorite Color      Which is your favorite color?       Orange          8
1   Favorite Color      Which is your favorite color?       Red             4
1   Favorite Color      Which is your favorite color?       Yellow          3
1   Favorite Color      Which is your favorite color?       Green           1
1   Favorite Color      Which is your favorite color?       Black           0       /* Shows 0 votes for Black */
2   Favorite Cheese     Which is your favorite cheese?      Mozarella       7
2   Favorite Cheese     Which is your favorite cheese?      Parmesan        4
2   Favorite Cheese     Which is your favorite cheese?      Swiss Cheese    3
2   Favorite Cheese     Which is your favorite cheese?      Colby-Jac       3
2   Favorite Cheese     Which is your favorite cheese?      Monterey Jack   2

You can see this here -> http://sqlfiddle.com/#!9/c4a9e5/2

Hope this helps!!!

Answer 2

So many answers are in SQL here. I'll answer in PHP.

So this works for Laravel.

You need to have 3 models. Run php artisan make:model to make a model (I'm guessing you already know).

Post.php, Answer.php, Votes.php

Use Eloquent to make relations.

On your Post.php file, make a one-to-many relation, like so:

public function answers()
{
    return $this->hasMany('App\Answer', 'post_id', 'id');
}

On your Answer.php file, make another one-to-many relation, like so

public function votes()
{
    return $this->hasMany('App\Votes', 'answer_id', 'id');
}

Now make a belongs to relation on the same file:

public function post()
{
    return $this->belongsTo('App\Post', 'post_id', 'id');
}

Now you should be able to do something like:

$answer = Answer::first();
$answer->post //This should get you the post the answer belongs to.

$answer->votes //Retuns all the votes an answer has. You can iterate through this array.

Thats pretty much it.

Answer 3

SELECT p.id
     , p.title
     , p.content
     , a.id
     , a.content
     , SUM(ISNULL(av.vote,0)) AS TotalVotes 
  FROM posts AS p 
 INNER 
  JOIN answers AS a 
    on p.id=a.post_id 
  LEFT 
 OUTER 
  JOIN answer_votes AS av 
    on av.answer_id=a.id 
 GROUP 
    BY p.id
     , p.title
     , p.content
     , a.id
     , a.content 
 ORDER  
    BY TotalVotes

Answer 4

You need an INNER JOIN to get data of related tables

Try something like this:

select p.id,a.id,count(*) from posts p
INNER JOIN answers a ON p.id=a.post_id
INNER JOIN answer_votes v ON a.id=v.answer_id
where  a.id=v.answer_id and p.id=a.post_id and p.id=1 
group by p.id,a.id;