Get previous and next record with two order criteria

Question

I have a SQL Query with two ORDER BY criterias. How I can get the correct previous and next position for every record?

My Solution works as long the actual and previous or next record match the same first ORDER BY criteria.

Sample data

http://sqlfiddle.com/#!9/409b3/3

Target: How I can get for id 100026 the previous id 100018 and the next id 100003?

Schema

CREATE TABLE `profiles` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `rnd_order` INT(11) NOT NULL,
  `profile_status` INT(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `rnd_order` (`rnd_order`),
  KEY `profile_status` (`profile_status`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `profile_photos` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `member_id` INT(11) DEFAULT NULL,
  `photo` VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `photo_status` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  KEY `photo_status` (`photo_status`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Select query

SELECT profiles.id,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo
FROM
`profiles`  
LEFT JOIN profile_photos 
ON (
  profiles.id = profile_photos.member_id AND profile_photos.photo_status = 1
) 
WHERE (profile_photos.photo_status = 1 OR   
profile_photos.photo_status is null)
GROUP BY profiles.id 
ORDER BY photo_status DESC, profiles.rnd_order DESC;

My Solution (it don't work for the case where order by criteria of previous or next record is different)

Previous

SELECT profile.id,,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo
FROM
          `profiles`  
          JOIN profile_photos 
            ON (
              profiles.id = profile_photos.member_id AND profile_photos.photo_status = 1
            ) 
AND (profile_photos.photo_status = 1 AND profiles.rnd_order < {actual_profile_id}) 
ORDER BY profile_photos.photo_status DESC, profiles.rnd_order ASC
LIMIT 1

Next

SELECT profile.id,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo
FROM
          `profiles`  
          JOIN profile_photos 
            ON (
              profiles.id = profile_photos.member_id AND profile_photos.photo_status = 1
            ) 
AND ((profile_photos.photo_status = 1 AND profiles.rnd_order > {actual_profile_id}) OR (profile_photos.photo_status IS NULL))
ORDER BY profile_photos.photo_status DESC, profiles.rnd_order DESC
LIMIT 1

Answer 1

select s2.*, @nv NXV, (@nv := id) NNXV from (
select s1.*,
@pv PRV, (@pv := id) NPRV, (@rn := @rn + 1) RN
from (
SELECT DISTINCT profiles.id,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo,
profiles.rnd_order
FROM
`profiles`
LEFT JOIN profile_photos
ON (
  profiles.id = profile_photos.member_id
  AND profile_photos.photo_status = 1
), (select @rn := 0)  RNT
WHERE (profile_photos.photo_status = 1 OR
profile_photos.photo_status is null)
GROUP BY profiles.id
ORDER BY photo_status DESC, profiles.rnd_order DESC) s1
) s2
order by RN desc;

see http://sqlfiddle.com/#!9/409b3/20

you will find the info you wanted in columns NXV and PRV

This fiddle http://sqlfiddle.com/#!9/409b3/47 shows the query for a specific profile id.

select p.*, pf.*,
       pmax.id, pmax.rnd_order,
       pmin.id, pmin.rnd_order
  from profiles p left outer join
       profile_photos pf
       on  pf.member_id = p.id
       and pf.photo_status = 1,
       profiles pmax,
       profiles pmin
  where p.id = 100024
    and pmax.rnd_order = (select ifnull(max(rnd_order),p.rnd_order) from profiles pm where pm.rnd_order < p.rnd_order)
    and pmin.rnd_order = (select ifnull(min(rnd_order),p.rnd_order) from profiles pm where pm.rnd_order > p.rnd_order)
;

Can you check this for performance? (explain plan looks promising)