Select adjacent rows in mysql – but when using alphabetical order of specific column, not numeric order of ID

I have a wordpress install with custom post types where I would like to find the ‘previous’ and ‘next’ custom post type object via mysql.

I have read many questions/answers on SO regarding how to achieve this when using the ID. The issue is that a lot of these objects were input at different times, so their numeric ‘id’ order does not necessarily correspond with their string post_title order.

eg:

   ID   |   post_title
   7    |       a
   1    |       b
   3    |       c

The SQL query:

SELECT ID, post_title 
FROM wp_posts 
WHERE post_type = 'custom_type' 
ORDER BY post_title ASC

Gives me the results. If I know the ID, for example b, 1 from above, how could I find that, when ordered by post_title, that the adjacent posts are 7 and 3?

Answer

You can do this with union all:

(SELECT ID, post_title 
 FROM wp_posts 
 WHERE post_type = 'custom_type' and
       post_type < (select post_title from wp_posts where post_type = 'custom_type' and id = 1)
 ORDER BY post_title DESC
 LIMIT 1, 1
)
UNION ALL
(SELECT ID, post_title 
 FROM wp_posts 
 WHERE post_type = 'custom_type' and
       post_type > (select post_title from wp_posts where post_type = 'custom_type' and id = 1)
 ORDER BY post_title ASC
 LIMIT 1
)
ORDER BY ID;