MySQL exercises: getting the previous and the next message for each thread

Here's an interesting problem: we have a table thread and table messages, containing messages threaded in each thread. The question is if we have given message id, how to retrieve this message along with the previous and the next message in the same thread.

The problem

Here's the database along with some test data:

create table threads(id int, title varchar(80));
create table messages(id int, thread_id int, contents varchar(255));
insert into threads(id, title) values
(1, 'thread 1'), (2, 'thread 2'), (3, 'thread 3');
insert into messages(id, thread_id, contents) values
(1, 1, 'p1 message 1'), (2, 2, 'p2 message 1'), (3, 3, 'p3 message 1'),
(4, 3, 'p3 message 2'), (5, 1, 'p1 message 2'), (6, 2, 'p2 message 2'),
(7, 2, 'p2 message 3'), (8, 1, 'p1 message 3'), (9, 3, 'p3 message 3');

We have the message with id=5 and thread_id=1 and we want to get this message, the message from right before this one and the one after this.

Solution number one: 2 + 1 queries

Let's examine the following fragment:

$id = 5;
$thread_id = 1;

$res_prev = mysql_query("select max(id) as prev_id from messages where thread_id=$thread_id and id<$id");
$row_prev = mysql_fetch_assoc($res_prev);
$id_prev = $row_prev['prev_id'];

$res_next = mysql_query("select min(id) as next_id from messages where thread_id=$thread_id and id>$id");
$row_next = mysql_fetch_assoc($res_next);
$id_next = $row_prev['next_id'];

$res_all = mysql_query("select * from messages where id in ($id, $id_prev, $id_next) order by id asc");

Here we just issue two additional queries to retrieve the next and the previous id and then use them in the where clause of the final query.

Solution number two: single query

Examine the following approach: let's determine which is the previous message in the thread. Then count 3 messages and you'll get the previous, the current and the next message. The code for this will be:

$res_all = mysql_query("select * from messages
where id>ifnull((select max(id) from messages WHERE id<'$id' AND thread_id='$thread_id'), 0) and thread_id='$thread_id'
order by id asc
limit 3");


No comments yet

Back to articles list

This page was last modified on 2024-03-03 09:32:36