Geek Answers Handbook

Receive total in addition to score if user voted

I have three tables that I join. submissions , submissions_votes and users .

I want to find out how much useful useful data there is (which is the sum of counting all submissions_votes ), and I got this.

I also want to return the counter (boolean, rather) 0 or 1 if user_id of sv.user_id refers to view view. user_id is passed to the WHERE .

  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 connection (on sv.user_id = u.id ), but what would I choose? Then would I group using 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 | | +-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
+6
source share
6 answers

You can check if sv.user_id = input user_id with CASE and SUM up (grouped by each send). If SUM is 1, then user_id has a representation, otherwise not. So, your user_id entry will go into the CASE function.

In addition, COALESCE(SUM(sv.up), 0) requires grouping by any column in the submissions and users tables.

The following is a table-based query in 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 you need to select more columns from the submissions table, then they must be grouped or aggregated)

+1
source

I do not think you need an extra connection. Just a boolean expression in 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;
+1
source

I use mssql, so I don’t know for sure if my answer will work for mysql. In mssql, the query in question will not work without a group. If you left the group, you must include srsv.submission_id in the group. Assuming you have a limitation that ensures that only on submit_vote for each user 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
0
source

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
0
source

To get the total number for all views and the total number for user-only views (or other user-specific aggregates), you can join the submit_votes table twice, with a different alias for the second connection to the table. This will allow any aggregation in Submission_Vote user data.

This is pseudo code - the GROUP BY s command. * should be replaced by the fields that you select and group 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
0
source

I don’t understand what the boolean value should indicate, but I assume that it has at least 1 vote provided by user_id in the votes view, because if all the votes in the video_ view must be associated with user_id, then the boolean value will be superfluous, as you can just look at the score.

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
0
source

More articles:


All Articles