How to determine the position (rank) based on the rating of users or products?
there is a table
CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(255) NOT NULL,
positions int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (id),
)
ENGINE = INNODB
AVG_ROW_LENGTH = 630
CHARACTER SET utf8
COLLATE utf8_unicode_ci;
if you want it in a single query while also accounting for the fact that people or products may have
identical ratings, you can do it like this, for example, but you'll need to store the ratings in a separate table
SET @position = 0;
DELETE
FROM raiting_place_user;
INSERT INTO raiting_place_user (id, raiting_place, rating)
SELECT
id,
@position := @position + 1 AS pos,
rating
FROM (SELECT
*
FROM user
WHERE utype = 0
ORDER BY rating DESC) user
WHERE utype = 0;
CREATE TABLE raiting_place_user (
id int(11) NOT NULL,
raiting_place int(11) NOT NULL DEFAULT 0,
rating int(11) DEFAULT NULL COMMENT 'just for testing (can be removed)',
PRIMARY KEY (id),
CONSTRAINT FK_raiting_place_user_user_id FOREIGN KEY (id)
REFERENCES user (id) ON DELETE RESTRICT ON UPDATE RESTRICT
)
ENGINE = INNODB
AVG_ROW_LENGTH = 512
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'user rating positions';
it's better to wrap the queries themselves in a procedure that's called by a trigger
CREATE DEFINER = 'root'@'127.0.0.1'
PROCEDURE update_positions()
BEGIN
SET @position = 0;
DELETE
FROM raiting_place_user;
INSERT INTO raiting_place_user (id, raiting_place, rating)
SELECT
id,
@position := @position + 1 AS pos,
rating
FROM (SELECT
*
FROM user
WHERE utype = 0
ORDER BY rating DESC) user
WHERE utype = 0;
END
trigger on user deletion
CREATE
DEFINER = 'root'@'127.0.0.1'
TRIGGER deleteuser
AFTER DELETE
ON user
FOR EACH ROW
BEGIN
CALL update_positions();
END
for inserts, you can't make a trigger work — call it yourself
CALL update_positions();
and for updates
CREATE
DEFINER = 'root'@'127.0.0.1'
TRIGGER addeditstatus
AFTER UPDATE
ON user
FOR EACH ROW
BEGIN
IF (@DISABLE_TRIGGERS IS NULL) AND (OLD.rating <> NEW.rating) THEN
# rating change and recalculation of positions
CALL update_positions();
END IF;
END
but can the results be written back with a single query into the main table (to be used for sorting)
??
yes, you can
CREATE DEFINER = 'root'@'127.0.0.1'
PROCEDURE copy_positions()
BEGIN
UPDATE user, raiting_place_user
SET user.positions = IF( ISNULL(raiting_place_user.raiting_place),0, raiting_place_user.raiting_place)
WHERE user.id = raiting_place_user.id;
END
call it like this
CALL copy_positions();
Comments