Re: Voting SQL

[Jassim Rahma]

This is how I am inserting the rating:

IF EXISTS (SELECT nuwab_vote_id FROM nuwab_votes WHERE nuwab_id = param_nuwab AND user_id = param_user) THEN
BEGIN
UPDATE nuwab_votes SET
vote_rating = param_rating
WHERE nuwab_id = param_nuwab
AND user_id = param_user;
END;
ELSE
BEGIN
INSERT INTO nuwab_votes (nuwab_id, vote_rating, user_id, created_ip)
VALUES (param_nuwab, param_rating, param_user, param_ip);
END;
END IF;


nuwab Table

CREATE TABLE `nuwab` (
`nuwab_id` int(11) NOT NULL AUTO_INCREMENT,
`nuwab_uuid` varchar(255) DEFAULT NULL,
`nuwab_name` varchar(255) DEFAULT NULL,
`nuwab_description` varchar(255) DEFAULT NULL,
`is_active` bit(1) DEFAULT b'1',
`governorate` varchar(45) DEFAULT NULL,
`area` varchar(45) DEFAULT NULL,
`rating` int(11) DEFAULT '0',
PRIMARY KEY (`nuwab_id`)
) ENGINE=InnoDB AUTO_INCREMENT=141 DEFAULT CHARSET=utf8



nuwab_votes table

CREATE TABLE `nuwab_votes` (
`nuwab_vote_id` bigint(20) NOT NULL AUTO_INCREMENT,
`nuwab_id` int(11) DEFAULT NULL,
`user_id` bigint(20) DEFAULT NULL,
`vote_rating` double DEFAULT NULL,
`is_closed` bit(1) DEFAULT b'0',
`created_date` datetime DEFAULT CURRENT_TIMESTAMP,
`created_ip` varchar(255) DEFAULT NULL,
PRIMARY KEY (`nuwab_vote_id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8



This is my SQL to populate the data:

SELECT nuwab.nuwab_id, nuwab.nuwab_uuid, nuwab.nuwab_name,
nuwab.governorate, nuwab.area, IFNULL(nuwab_votes.vote_rating, 0) AS rating
FROM nuwab
LEFT JOIN nuwab_votes ON nuwab_votes.nuwab_id = nuwab.nuwab_id
WHERE nuwab_votes.user_id = 1001
GROUP BY nuwab.nuwab_id
ORDER BY nuwab.nuwab_name;