Добавлен порядок результатов по времени, но ранжируйте их по количеству голосов

Я пишу веб-приложение, в котором люди могут добавлять идеи и голосовать за них. При выводе идей я хочу, чтобы они были упорядочены по их общему количеству голосов или добавленному времени, но всегда имеют рейтинг, основанный на подсчете голосов.

Вот что у меня сейчас:

function get_ideas($status, $sortby, $count, $page){
$offset = ($page - 1) * $count;
$dbh = db_connect();
if($sortby === 'popular'){
$stmt = $dbh->prepare("SELECT i.idea_id, i.idea_datetime, i.user_id, i.idea_title, i.idea_text,
i.idea_vote_count, u.user_name, @curRank := @curRank + 1 AS rank
FROM ideas i
JOIN (SELECT @curRank := :rankoffset) AS q
JOIN users u ON i.user_id = u.user_id
WHERE idea_status = :idea_status
ORDER BY idea_vote_count DESC
LIMIT :count
OFFSET :offset;");
} else {
$stmt = $dbh->prepare("HOW DO I DO THIS???");
}
$stmt->bindParam(':idea_status', $status, PDO::PARAM_STR);
$stmt->bindParam(':rankoffset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':count', $count, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = NULL;
$dbh = NULL;
return $result;
}

Код в блоке «if» работает как задумано — он возвращает массив, упорядоченный по «idea_vote_count» с правильными рангами.

Однако я понятия не имею, каким должно быть второе утверждение. Упорядочение по добавленному времени будет достигнуто достаточно легко, просто изменив «idea_vote_count» в предложении «ORDER BY» на «idea_id». Как мне получить звания, хотя? Я не мог ни придумать, ни найти решение, которое не включало бы сохранение рангов в самой таблице.

Надеюсь, я четко объяснил свою проблему, но на всякий случай:

Как мне получить такой стол:

idea_id | idea_vote_count
1 |              20
2 |              40
3 |              30
4 |               5

Чтобы получить вывод, как это:

rank | idea_id | idea_vote_count
4 |       4 |               5
2 |       3 |              30
1 |       2 |              40
3 |       1 |              20

Кроме того, я новичок в PHP и MySQL, поэтому, если вы обнаружите какие-либо другие проблемы, пожалуйста, укажите их.

Я с нетерпением жду вашего совета. Спасибо 🙂

РЕДАКТИРОВАТЬ: для клубники:

Моя таблица идей:

CREATE TABLE `ideas`(
`idea_id` int NOT NULL AUTO_INCREMENT,
`idea_datetime` datetime NOT NULL,
`user_id` int NOT NULL,
`idea_title` varchar(48) NOT NULL,
`idea_text` text NOT NULL,
`idea_vote_count` int NOT NULL DEFAULT 0,
`idea_status` varchar(16) NOT NULL DEFAULT 'active',
PRIMARY KEY(`idea_id`),
FOREIGN KEY(`user_id`) REFERENCES users(`user_id`))
ENGINE=INNODB;

Примеры идей были сгенерированы следующим сценарием. Затем я вручную изменил idea_vote_count строки от 100 до 5.

$i = 1;
set_time_limit(150);
while (i<101) {
$datetime = date('Y-m-d h:m:s');
$dbh->exec(INSERT INTO `ideas` (`idea_datetime`, `user_id`, `idea_title`, `idea_text`, `idea_vote_count`, `idea_status`)
VALUES ('{$datetime}', $i, 'Title{$i}', 'Text{$i}', $i, 'active');
$i++
sleep(1);
}

Это то, что я закончил после включения SQL Strawberry в мою функцию:

function get_ideas($status, $sortby, $count, $page){
$offset = ($page - 1) * $count;
$dbh = db_connect();
if($sortby === 'popular'){
$stmt = $dbh->prepare("SELECT i.idea_id, i.idea_datetime, i.user_id, i.idea_title, i.idea_text, i.idea_vote_count, u.user_name, @curRank := @curRank + 1 AS rank
FROM ideas i
JOIN (SELECT @curRank := :rankoffset) AS q
JOIN users u
ON i.user_id = u.user_id
WHERE idea_status = :idea_status
ORDER BY idea_vote_count DESC
LIMIT :count
OFFSET :offset;");
} else {
$stmt = $dbh->prepare("SELECT n.*
FROM (
SELECT i.idea_id, i.idea_datetime, i.user_id, i.idea_title, i.idea_text, i.idea_vote_count, u.user_name, @curRank := @curRank + 1 AS rank
FROM ideas i
JOIN (SELECT @curRank := :rankoffset) AS q
JOIN users u
ON i.user_id = u.user_id
WHERE idea_status = :idea_status
ORDER BY idea_vote_count DESC
LIMIT :count
OFFSET :offset) n
ORDER BY idea_id DESC;");
}
$stmt->bindParam(':idea_status', $status, PDO::PARAM_STR);
$stmt->bindParam(':rankoffset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':count', $count, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = NULL;
$dbh = NULL;
return $result;
}

Как видите, функция принимает в качестве аргументов $ count и $ page (обычно они имеют значение $ _REQUEST [‘count / page’]) и вычисляет смещение и лимит на основе их. Это очень важно, потому что я не хочу показывать все идеи пользователям одновременно, я хочу разделить их на несколько страниц. Однако это портит SQL выбора / ранжирования следующим образом:

Когда $ page = 1 и $ count = 100, вы получаете LIMIT 100 OFFSET 0, и скрипт работает как задумано — он показывает самую последнюю строку (строку 100), поскольку первая занимает 96 место (только строки 1, 2, 3, 4 имеют меньший подсчет голосов), за которым следуют другие последние строки с номерами 1, 2, 3 и так далее.

Однако, когда $ page = 1 и $ count = 10, вы получите LIMIT 10 OFFSET 0, и сценарий сначала выведет строку 99, потому что она имеет самый высокий рейтинг, но не самый последний. Строка 100 становится первым результатом в наборе результатов, когда $ page = 10 (строки с самым низким рейтингом и строки OLDEST, несмотря на то, что строка 100 является самой последней).

Я мог бы технически выбрать всю таблицу, а затем обработать нумерацию страниц в PHP, но я боюсь, как это повлияет на производительность.

EDIT2: я переместил OFFSET и LIMIT во внешний SELECT, и теперь все работает как положено. Побочным эффектом этого является то, что внутренний SELECT выбирает всю таблицу, но, надеюсь, он не станет слишком большим для обработки сервером. Это решение, которое я придерживаюсь в настоящее время. Он основан на SQL Strawberry, поэтому я отмечу это как ответ. Спасибо, Клубника 🙂

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(idea_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,idea_vote_count INT NOT NULL
);

INSERT INTO my_table VALUES
(1 ,20),
(2 ,40),
(3 ,30),
(4 ,5);

SELECT n.*
FROM
( SELECT x.*
, @i:=@i+1 rank
FROM my_table x
, (SELECT @i:=0) vars
ORDER
BY idea_vote_count DESC
) n
ORDER
BY idea_id DESC;
+---------+-----------------+------+
| idea_id | idea_vote_count | rank |
+---------+-----------------+------+
|       4 |               5 |    4 |
|       3 |              30 |    2 |
|       2 |              40 |    1 |
|       1 |              20 |    3 |
+---------+-----------------+------+