I have gallery of images. Each image has a rating based on how many users voted "i like this". The MySQL table lokes like this:
id |image |rating
------------------------------------
166 |6.png |9
165 |8.png |9
189 |1.png |8
171 |99.png |8
169 |56.png |8
155 |34.png |8
265 |7.png |7
754 |86.png |6
166 |37.png |4
342 |95.png |2
99 |35.png |0
76 |34.png |0
44 |3.png |0
8 |22.png |0
The task is: make it possible to view gallery sorted by rating. It's easy to list images using ORDER BY rating DESC, id DESC but problem comes out when user clickes on an image and I must show "Previous image" and "Next Image" buttons next to the opened image.
Suppose we are now looking at image with id=169 from example table:
id |image |rating
------------------------------------
169 |99.png |8
How can I write my query, to get previous image (id=171)? The problem is, that primary ordering is by rating (which is not unique) and secondary is by unique id. Which query will give me correct previous image in any situation.
I've tried:
SELECT *
FROM images
WHERE rating >= 8 AND id >=169
ORDER BY rating, id
LIMIT 1
but it does not do, because id >=169 must be checked only if rating is the same.
I'm a bit confused, please help me out.
UPDATE
Figured this one on my own. Assuming that I have the rating and id values for current record, the query to get previous record would be:
SELECT *
FROM `images`
WHERE
(`rating` = 8 AND `id` > 169)
OR `rating` > 8
ORDER BY `rating`, `id`
LIMIT 1
If rating = 8 AND id > 169 is not evaluated, then rating > 8 goes into play. Turned out pretty simple.
Thanks everybody! Accepting the answer with most upvotes.
SELECT * FROM images ORDER BY rating DESC, id LIMIT $n, 1
where $n is a counter starting at 0. Ordering by id makes sure that subsequent calls always keep the same order. Thus next image would be $n+1, previous $n-1.
Additionally, you could always fetch 2 images if you want to make sure that there is a next image to the current one (users certainly don't like clicking 'next' to get a 404 error).
EDIT: new requirement from comment
hmm, you could try calculating a minimum distance from you're current image.
SELECT * from images
WHERE id < $current[id] AND rating >= $current[rating]
ORDER by ((abs($current[rating] - rating) << 32) + abs($current[id] - id))
LIMIT 1
SELECT * from images
WHERE id > $current[id] AND rating <= $current[rating]
ORDER by ((abs($current[rating] - rating) << 32) + abs($current[id] - id))
LIMIT 1
don't ask me anything about performance though ;)
Get all the entries in one query, put it into a php array, and work with that instead!
You're looking for a cursor oriented approach. The other quite easy method is to select just all IDs via a certain sorting criteria, save those in your application, then browse and select using the IDs and queries specifically selecting the row you want.
Try this one, replace the test to your table name and replace the curr.id =169 to current page id
SELECT curr.id AS currid, curr.image as curr_img,
prev.id AS previd, prev.image as prev_img,
next.id AS nextid, next.image as next_img
FROM test curr
LEFT JOIN test prev
ON prev.id != curr.id
AND (
prev.rating > curr.rating
OR (
prev.rating = curr.rating
AND prev.id > curr.id
)
)
LEFT JOIN test next
ON next.id != curr.id
AND (
next.rating < curr.rating
OR (
next.rating = curr.rating
AND next.id < curr.id
)
)
WHERE curr.id =169
ORDER BY prev.rating ASC , next.rating DESC , prev.id ASC , next.id DESC
LIMIT 1
I remembered an article I read on the MySQL performance blog about pagination and knocked this example up which might prove helpful (@row_id is redundant)
drop table if exists gallery;
create table gallery
(
id int unsigned not null,
image varchar(255) not null,
rating tinyint unsigned default 0
)
engine=innodb;
insert into gallery values
(166,'6.png',9),(165,'8.png',9),(189,'1.png',8),
(171,'99.png',8),(169,'56.png',8),(155,'34.png',8),
(265,'7.png',7),(754,'86.png',6),(37,'37.png',4),
(342,'95.png',2),(99 ,'35.png',0),(76 ,'34.png',0),
(44 ,'3.png',0),(8 ,'22.png',0), (1001 ,'1001.png',0);
drop procedure if exists list_gallery_paged;
delimiter #
create procedure list_gallery_paged
(
in p_last_id int unsigned,
in p_last_rating tinyint unsigned
)
proc_main:begin
set @row_id = 0;
if p_last_id <= 0 then
select @row_id:=@row_id+1 as row_id, g.*
from gallery g order by rating desc, id desc limit 4;
else
select @row_id:=@row_id+1 as row_id, g2.*
from gallery g inner join gallery g2 on g.id = g2.id
where
g.rating <= p_last_rating and (g.id < p_last_id or g.rating < p_last_rating)
order by
g.rating desc, g.id desc limit 4;
end if;
end proc_main #
delimiter ;
-- in pages of 4 (use all rows)
select g.* from gallery g order by rating desc, id desc;
call list_gallery_paged(0,0);
call list_gallery_paged(171,8);
call list_gallery_paged(754,6);
call list_gallery_paged(99,0);
call list_gallery_paged(8,0);
-- one at a time (use top row only)
select g.* from gallery g order by rating desc, id desc;
call list_gallery_paged(265,7);
call list_gallery_paged(754,6);
call list_gallery_paged(37,4);
call list_gallery_paged(342,2);
call list_gallery_paged(1001,0);
call list_gallery_paged(99,0);
call list_gallery_paged(76,0);
call list_gallery_paged(44,0);
call list_gallery_paged(8,0);