Ordering a list with reference to child elements

Hi,
I have a simple message board system. Each message is either a main
message, or a reply to one of the main messages (i.e. there are only
two tiers of messages). I would like to be able to show a list of main
messages showing which messages have the most recent replies. However,
I also need to include in this list (in appropriate order) single
messages that have no replies, so if there were four main messages
(numbered 1,3,2 and 6 below) and two of them had replies, with the
messages ordered from 1 (first sent) to 7 (most recently sent):

1 - replies: 5
3
2 - replies: 4, 7
6

then the list that I'm trying to achieve would look like this:

2
6
1
3

Any advice on the best way to go about this would be appreciated.
Michael

diversarts wrote:
> Hi,
> I have a simple message board system. Each message is either a main
> message, or a reply to one of the main messages (i.e. there are only
> two tiers of messages). I would like to be able to show a list of main
> messages showing which messages have the most recent replies. However,
> I also need to include in this list (in appropriate order) single
> messages that have no replies, so if there were four main messages
> (numbered 1,3,2 and 6 below) and two of them had replies, with the
> messages ordered from 1 (first sent) to 7 (most recently sent):
>
> 1 - replies: 5
> 3
> 2 - replies: 4, 7
> 6
>
> then the list that I'm trying to achieve would look like this:
>
> 2
> 6
> 1
> 3
>
> Any advice on the best way to go about this would be appreciated.
> Michael

So, assuming a structure like this:

message_id*, parent_id, message

where root messages are those whose parent is null, then something
like this should work
(untested)

SELECT m1.message_id
FROM messages m1
LEFT JOIN messages m2
ON m2.parent_id = m1.message_id
WHERE m1.parent_id IS NULL
ORDER BY m2.message_id DESC, m1.message_id DESC

SELECT m1.message_id
FROM messages m1
LEFT JOIN messages m2
ON m2.parent_id = m1.message_id
WHERE m1.parent_id IS NULL
ORDER BY m2.message_id DESC, m1.message_id DESC

That's a lot closer than I managed to get, but what it's returning
(based on the original example) is this:

2
6
1
2
3
2
1

I tried adding 'SELECT DISTINCT' which simply gave a standard list
ordered by which parent message was most recent. If it makes any
difference to the solution, in my table the parent id is set to be the
same as the message id in all posts that are parents, which means that
all messages in a thread (including the parent) have the same parent
id, except in the list of main posts, which is a list of all messages
where the message id is the same as the parent id (although I set
another field to identify all parent posts to speed up the query). I
was wondering whether there was some way to look at each group with
the same parent_id and order them by which group contained the highest
msg_id, but haven't worked out whether this is feasible or not.
Michael

diversarts wrote:
> SELECT m1.message_id
> FROM messages m1
> LEFT JOIN messages m2
> ON m2.parent_id = m1.message_id
> WHERE m1.parent_id IS NULL
> ORDER BY m2.message_id DESC, m1.message_id DESC
>
> That's a lot closer than I managed to get, but what it's returning
> (based on the original example) is this:
>
> 2
> 6
> 1
> 2
> 3
> 2
> 1
>
> I tried adding 'SELECT DISTINCT' which simply gave a standard list
> ordered by which parent message was most recent. If it makes any
> difference to the solution, in my table the parent id is set to be the
> same as the message id in all posts that are parents, which means that
> all messages in a thread (including the parent) have the same parent
> id, except in the list of main posts, which is a list of all messages
> where the message id is the same as the parent id (although I set
> another field to identify all parent posts to speed up the query). I
> was wondering whether there was some way to look at each group with
> the same parent_id and order them by which group contained the highest
> msg_id, but haven't worked out whether this is feasible or not.
> Michael

OK. Yeah, in this scenario you need a slightly different query. I'm
going to rename the parent_id to thread_id because it seems a bit
bizarre that a message can be its own parent...

SELECT m1.thread_id
FROM messages m1
LEFT JOIN messages m2
ON m1.thread_id = m2.thread_id
AND m1.message_id < m2,message_id
WHERE m2.message_id IS NULL
ORDER BY m2.message_id DESC

You can probably see from this that in your structure there's no need
for the root message to have the same id as the thread, Instead, to
find the root message, just reverse the < sign. For a clearer
understanding of what's going on, try this query instead:

SELECT *
FROM messages m1
LEFT JOIN messages m2
ON m1.thread_id = m2.thread_id
AND m1.message_id < m2,message_id
WHERE 1
ORDER BY m2.message_id DESC

> SELECT *
> FROM messages m1
> LEFT JOIN messages m2
> ON m1.thread_id = m2.thread_id
> AND m1.message_id < m2,message_id
> WHERE 1
> ORDER BY m2.message_id DESC- Hide quoted text -

Strawberry - thank you for responding. That's giving a rather odd
result - it's showing the replies mixed amongst the main messages, and
also showing multiple entries of the same main message. By adding 'AND
m1.message_id = m1.thread_id I can limit it to showing just the main
entries, but then it's still showing duplicates of the same result:

2
6
1
2
3
2
1

Your solution solves the difficult half the problem - it successfully
returns a list of messages in order of most recent. Now I need to work
out how to strip out the duplicate entries.
Michael

On 11 Jun, 00:56, diversarts <m.k.b...@btinternet.com> wrote:
> > SELECT *
> > FROM messages m1
> > LEFT JOIN messages m2
> > ON m1.thread_id = m2.thread_id
> > AND m1.message_id < m2,message_id
> > WHERE 1
> > ORDER BY m2.message_id DESC- Hide quoted text -
>
> Strawberry - thank you for responding. That's giving a rather odd
> result - it's showing the replies mixed amongst the main messages, and
> also showing multiple entries of the same main message. By adding 'AND
> m1.message_id = m1.thread_id I can limit it to showing just the main
> entries, but then it's still showing duplicates of the same result:
>
> 2
> 6
> 1
> 2
> 3
> 2
> 1
>
> Your solution solves the difficult half the problem - it successfully
> returns a list of messages in order of most recent. Now I need to work
> out how to strip out the duplicate entries.
> Michael


That's why you should use the first query, not the second one:

SELECT m1.thread_id
FROM messages m1
LEFT JOIN messages m2
ON m1.thread_id = m2.thread_id
AND m1.message_id < m2.message_id
WHERE m2.message_id IS NULL
ORDER BY m2.message_id DESC

> SELECT m1.thread_id
> FROM messages m1
> LEFT JOIN messages m2
> ON m1.thread_id = m2.thread_id
> AND m1.message_id < m2.message_id
> WHERE m2.message_id IS NULL
> ORDER BY m2.message_id DESC

That still doesn't seem to work - thank you though!
Michael

On Jun 11, 12:28 pm, diversarts <m.k.b...@btinternet.com> wrote:
> > SELECT m1.thread_id
> > FROM messages m1
> > LEFT JOIN messages m2
> > ON m1.thread_id = m2.thread_id
> > AND m1.message_id < m2.message_id
> > WHERE m2.message_id IS NULL
> > ORDER BY m2.message_id DESC
>
> That still doesn't seem to work - thank you though!
> Michael

Oops, I meant:

SELECT m1.thread_id
FROM messages m1
LEFT JOIN messages m2 ON m1.thread_id = m2.thread_id
AND m1.message_id < m2.message_id
WHERE m2.message_id IS NULL
ORDER BY m1.message_id DESC

That should work!

On 11 Jun, 12:28, diversarts <m.k.b...@btinternet.com> wrote:
> > SELECT m1.thread_id
> > FROM messages m1
> > LEFT JOIN messages m2
> > ON m1.thread_id = m2.thread_id
> > AND m1.message_id < m2.message_id
> > WHERE m2.message_id IS NULL
> > ORDER BY m2.message_id DESC
>
> That still doesn't seem to work - thank you though!
> Michael

How about you export the table schema with a bit of sample data So
that we can see precisely what doesn't seem to work?

On 11 Jun, 12:48, strawberry <zac.ca...@gmail.com> wrote:

> SELECT m1.thread_id
> FROM messages m1
> LEFT JOIN messages m2 ON m1.thread_id = m2.thread_id
> AND m1.message_id < m2.message_id
> WHERE m2.message_id IS NULL
> ORDER BY m1.message_id DESC
>
> That should work!


Strawberry - thank you for all your time - that works a treat.

Captain Paralytic - here's some code to try it on:

CREATE TABLE `messages` (
`message_id` mediumint(9) NOT NULL auto_increment,
`thread_id` mediumint(9) NOT NULL default '0',
`is_primary` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`message_id`),
KEY `thread_id` (`thread_id`)
);

INSERT INTO `messages` VALUES(1, 1, 1),(2, 2, 1),(3, 3, 1),(4, 2, 0),
(5, 1, 0),(6, 6, 1),(7, 2, 0);