The following query might be somewhere to start. It uses window functions which are a new feature in PostgreSQL 8.4. They'll allow us to calculate the message index and slice on it straight in the database.
SELECT * FROM (
SELECT message.*, row_number() OVER (
ORDER BY message.datecreated, message.id) AS index
FROM message, bugmessage
WHERE bugmessage.message = message.id
AND bugmessage.bug = 15
AND (message.parent IS NULL OR message.parent IN (
SELECT message FROM bugmessage WHERE bug = 15))) AS indexed_messages
WHERE index > 2
AND index <= 4;
The following query might be somewhere to start. It uses window functions which are a new feature in PostgreSQL 8.4. They'll allow us to calculate the message index and slice on it straight in the database.
SELECT * FROM (
row_ number( ) OVER ( datecreated, message.id) AS index
SELECT message.*,
ORDER BY message.
FROM message, bugmessage
WHERE bugmessage.message = message.id
AND bugmessage.bug = 15
AND (message.parent IS NULL OR message.parent IN (
SELECT message FROM bugmessage WHERE bug = 15))) AS indexed_messages
WHERE index > 2
AND index <= 4;