4 июн. 2011 г.

Mysql: get next\prev id with exact ordering

Task is to show previous and next link to smth. For example next\previous product in category.
It is hard to create this links because ids are not in a sequance (some entries were deleted or there is an order).

And, naturally, we dont  want to select all ids and search in loop :)

Ноwto do it in one sql-query

Lets have in our table `main_table` fields `id` and `name`. Last one in order to sort.
CREATE TABLE `main_table`(
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NULL,
  PRIMARY KEY (`id`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT;

REPLACE INTO 
  main_table (id, name) 
VALUES 
  (1, '1st entry'), 
  (2, '2nd entry'), 
  (3, '3rd entry'), 
  (5, '4th entry, but id is 5');


Getting prev ID.
SELECT 
  t1.id, t1.name, t1.prev as prev_id
FROM 
(SELECT mt.id, mt.name, @prev_id as prev, @prev_id := id 
FROM `main_table` mt, (SELECT @prev_id:=0) ni 
ORDER BY name ASC) as t1;
Here is hack to declare variable @prev_id variable which contains:
(SELECT @prev_id:=0) pi
And:
@prev_id as prev, @prev_id := id
Mysql place @prev_id to column 'prev' of result set, and than set @prev_id to current id, which on the next iteration will be previous. For the first entry @prev_id will be 0.

Getting next ID.
Basically, to get next ID we need just change ordering. In our example from ASC to DESC
SELECT 
  t1.id, t1.name, t1.next as next_id
FROM 
(SELECT mt.id, mt.name, @next_id as next, @next_id := id 
FROM `main_table` mt, (SELECT @next_id:=0) ni 
ORDER BY name DESC) as t1;
And finally:
SELECT 
  t1.next as next_id
FROM 
(SELECT mt.id, mt.name, @next_id as next, @next_id := id 
FROM `main_table` mt, (SELECT @next_id:=0) ni 
ORDER BY name DESC) as t1
WHERE t1.id = 3;

Комментариев нет:

Отправить комментарий