Reputation: 2269
Get total count in addition of count if user voted
Got three tables I'm joining. submissions, submissions_votes, and users.
I want to find out how many total helpfulVotes there are (which is a sum of the count of all submissions_votes) and I've got that.
I also want to return a count (boolean, rather) of 0 or 1 if the user_id of sv.user_id relates to the submission being viewed. The user_id is passed in to the WHERE clause.
SELECT s.*,
u.username,
u.photo as userPhoto,
COALESCE(SUM(sv.up), 0) helpfulVotes
FROM
submissions s
LEFT JOIN submissions_votes sv on s.id = sv.submission_id WHERE u.id = ?
INNER JOIN users u
ON s.user_id = u.id
I know I need an additional join (on sv.user_id = u.id) but what would I select? Then would I group by sv.id?
Edit:
users table:
+----------------+------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| email | varchar(128) | NO | MUL | NULL | |
| username | varchar(23) | NO | | NULL | |
| type | enum('normal','admin') | NO | | normal | |
| about | varchar(255) | NO | | NULL | |
| photo | varchar(32) | NO | | NULL | |
+----------------+------------------------+------+-----+-------------------+-----------------------------+
submissions_votes table:
+---------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| submission_id | int(10) unsigned | NO | MUL | NULL | |
| when | datetime | NO | | NULL | |
| user_id | int(10) unsigned | NO | MUL | NULL | |
| up | tinyint(3) unsigned | NO | | NULL | |
| down | tinyint(3) unsigned | NO | | NULL | |
+---------------+---------------------+------+-----+---------+----------------+
submissions table:
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(255) | NO | MUL | NULL | |
| slug | varchar(255) | NO | | NULL | |
| description | mediumtext | NO | | NULL | |
| user_id | int(11) | NO | MUL | NULL | |
| created | datetime | NO | | NULL | |
| type | enum('tip','request') | NO | | NULL | |
| thumbnail | varchar(64) | YES | | NULL | |
| removed | tinyint(1) unsigned | NO | | 0 | |
| keywords | varchar(255) | NO | | NULL | |
| ip | int(10) unsigned | NO | | NULL | |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
Upvotes: 6
Views: 418
Answers (6)
Reputation: 32402
I'm not clear what the boolean is supposed to indicate but I assume it's whether there is at least 1 vote by the supplied user_id in submission_votes because if all votes in submission_votes are supposed to be related to user_id, then the boolean would be redundant as one could just look at the count.
I think subqueries are the way to go. Try something like this:
SELECT s.*,
u.username,
u.photo as userPhoto,
COALESCE(
(
SELECT SUM(sv.up) FROM
submission_votes sv
WHERE sv.submission_id = s.id
), 0) helpfulVotes,
(CASE
WHEN EXISTS (SELECT 1
FROM submission_votes
WHERE sv.submission_id = s.id
AND sv.user_id = ?)
THEN 1
ELSE 0
END) userSubmissionVoteExists
FROM
submissions s
INNER JOIN users u ON s.user_id = u.id
Upvotes: 0
Reputation: 8072
Check this
SELECT s.*,
u.username,
COALESCE(SUM(sv.up), 0) helpfulVotes,
IF (srsv.submission_id is null, 1, 0)
FROM
submissions s
LEFT JOIN submissions_votes sv on s.id = sv.submission_id
LEFT JOIN submissions_votes srsv on s.id = srsv.submission_id and s.user_id = {USER_ID}
INNER JOIN users u ON s.user_id = u.id
Upvotes: 0
Reputation: 1750
To get the total for all submissions , and the total for the submissions only for the user (Or other user specific aggregations) , you can join to the submission_votes table twice, with a different alias for the second connection to the table. This will allow any aggregation on user specific Submission_Vote details.
This is pseudo code - the GROUP BY s.* will have to be replaced by the fields you are selecting and grouping by:
SELECT
s.*
,u.username
,u.photo AS userPhoto
,COALESCE(SUM(sv.up), 0) AS helpfulVotes
,COALESCE(SUM(sv_user.up), 0) AS helpfulVotes_user
FROM
submissions s
LEFT JOIN submissions_votes sv
on s.id = sv.submission_id
LEFT JOIN submissions_votes sv_user
on s.id = sv_user.submission_id
INNER JOIN users u
ON s.user_id = u.id
WHERE
sv_user.user_id = @User_id
GROUP BY
s.*
,u.username
,u.photo
Upvotes: 0
Reputation: 5679
You can check if the sv.user_id = input user_id using CASE and SUM it up (grouped by each submission). If the SUM is 1, then the input user_id has a submission, otherwise not. So, your input user_id would go into the CASE function.
Also, the COALESCE(SUM(sv.up), 0) requires a grouping by whichever columns of submissions and users tables are selected.
The following is the query based on the tables in the SQL Fiddle here.
SELECT
s.id as submission_id,
s.title as submission_title,
MAX(u.email) as submission_user_email,
COALESCE(SUM(sv.up), 0) helpfulVotes,
SUM(CASE sv.user_id
WHEN ? THEN 1
ELSE 0
END) User_Submission
FROM
submissions s
LEFT JOIN submissions_votes sv on s.id = sv.submission_id
INNER JOIN USERS u
ON s.user_id = u.id
GROUP BY s.id, s.title;
(If more columns from the submissions table need to be selected, then they need to be either grouped or aggregated)
Upvotes: 1
Reputation: 1271211
I don't think you need an additional join. Just a boolean expression in the select:
SELECT s.*,
u.username,
u.photo as userPhoto,
COALESCE(SUM(sv.up), 0) helpfulVotes,
SUM(sv.user_id = u.id) as SubmissionUserMatches
FROM submissions s LEFT JOIN
submissions_votes sv
on s.id = sv.submission_id INNER JOIN
users u
ON s.user_id = u.id
GROUP BY s.id, u.username, u.photo;
Upvotes: 1
Reputation: 36
I use mssql so don't know for sure if my answer works for mysql. In mssql the query in the question would not work without a group by. if you did leave the group by out,You should include srsv.submission_id in the group by. Assuming you have a constraint that makes sure there is only on submission_vote per submission and user the following should work:
SELECT s.*,
u.username,
u.photo as userPhoto,
COALESCE(SUM(sv.up), 0) helpfulVotes
case when srsv.submission_id is null then 1 else 0 end SelfRelatedVote
FROM
submissions s
LEFT JOIN submissions_votes sv on s.id = sv.submission_id
Left JOIN submissions_votes srsv on s.id = srsv.submission_id and s.user_id = srsv.user_id
INNER JOIN users u
ON s.user_id = u.id
Upvotes: 0
Related Questions
- How can i count votes for a post
- MySQL vote count for each row each column
- How can I count the number of all votes per voter?
- Getting Total Votes Form mysql table and Compare it?
- How to count votes in mySQL tables?
- MySQL: count votes for users
- Mysql subquery sum of votes
- Counting votes and if user voted in MySQL table
- How to get sum of votes for an item in a DB?
- How do I tally votes in MySQL?