MySQL Voting Table, find the last vote of each user in the record and count based on the value

Question

Working with a database structure that I did not create and did not hope for some help with a simple MySQL query. I have a votes table with these fields:

vote_id = unique voting identifier

item_id = id of the entry that voted on

voter_id = ID of the member who entered the vote

vote_date = date of voting

vote = type of voting (1 or 2)

Each user can vote several times in the site entry specified by item_id . They can put a vote of 1, which is a "similar" vote, or a vote of 2, which is "unlike" a vote. Each time they vote, a new entry is created. I would like to be able to find the last vote value for each user in a particular item_id , and then be able to actually SUM or COUNT in the vote column if the vote value is "how" (value 1)

for instance

 vote_id item_id voter_id vote_date vote 60 9 27 1273770151 1 153 9 45 1274896188 1 163 9 3 1274918584 1 164 9 3 1275021495 2 1051 9 181 1290839090 1 

I want to get the latest vote values ​​for each user, and then count the number of votes 1 of them. In this case, # will be 3

 27 = 1 45 = 1 3 = 2 181 = 1 3 

Ideally, when I have a "rating" for each item_id , the query could SUM evaluate each of them in the total number of ALL current "likes" on the site.

Thanks for any help with this. I did my best to find the answer to this question, but nothing was right.

Great importance.

Answer 1

[EDIT:] I added values ​​(1052, 10, 3, 1290839091, 1) to better accomplish this problem, and I had to add "item_id" to the second query, which fetches the most recent votes.

---

Hooray! Finally, a stackoverflow question that I really can answer !!! I spent a week looking for something short and sweet and across my lane. Thanks for the interesting SQL problem!

First, you need to extract the newest voices. The combination (voter_id, item_id, vote_date) is best unique for this approach to work!

 SELECT voter_id, item_id, MAX(vote_date) AS vote_date FROM votes GROUP BY voter_id, item_id 

Results:

 +----------+---------+------------+ | voter_id | item_id | vote_date | +----------+---------+------------+ | 3 | 9 | 1275021495 | | 3 | 10 | 1290839091 | | 27 | 9 | 1273770151 | | 45 | 9 | 1274896188 | | 181 | 9 | 1290839090 | +----------+---------+------------+ 

And then you need to join the source table against these results. Sub-selection will do the job. Notice how the above request is copied and pasted into the connection below, but now it is named "tmp":

 SELECT v.* FROM ( SELECT voter_id, item_id, MAX(vote_date) AS vote_date FROM votes GROUP BY voter_id, item_id ) tmp INNER JOIN votes v ON ( v.vote_date = tmp.vote_date AND v.voter_id = tmp.voter_id AND v.item_id = tmp.item_id ) 

Results:

 +---------+---------+----------+------------+------+ | vote_id | item_id | voter_id | vote_date | vote | +---------+---------+----------+------------+------+ | 60 | 9 | 27 | 1273770151 | 1 | | 153 | 9 | 45 | 1274896188 | 1 | | 164 | 9 | 3 | 1275021495 | 2 | | 1051 | 9 | 181 | 1290839090 | 1 | | 1052 | 10 | 3 | 1290839091 | 1 | +---------+---------+----------+------------+------+ 

Hope you know what to do next ... Oh my gosh, I can't help myself, it's too sweet:

 SELECT v.item_id, SUM(2 - v.vote) AS likes, SUM(v.vote - 1) AS dislikes FROM ( SELECT voter_id, item_id, MAX(vote_date) AS vote_date FROM votes GROUP BY voter_id, item_id ) tmp INNER JOIN votes v ON ( v.vote_date = tmp.vote_date AND v.voter_id = tmp.voter_id AND v.item_id = tmp.item_id ) GROUP BY v.item_id 

Results:

 +---------+-------+----------+ | item_id | likes | dislikes | +---------+-------+----------+ | 9 | 3 | 1 | | 10 | 1 | 0 | +---------+-------+----------+ 

Answer 2

Not sure if this is what you are looking for?

SELECT COUNT(*) FROM votes WHERE vote = '1' AND item_id = '9'