Professional Web Applications Themes

Ordering a list with reference to child elements - MySQL

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 ...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: Ordering a list with reference to child elements


    diversarts wrote: 

    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

    strawberry Guest

  3. #3

    Default Re: Ordering a list with reference to child elements

    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 Guest

  4. #4

    Default Re: Ordering a list with reference to child elements


    diversarts wrote: 

    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

    strawberry Guest

  5. #5

    Default Re: Ordering a list with reference to child elements

    > SELECT * 

    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

    diversarts Guest

  6. #6

    Default Re: Ordering a list with reference to child elements

    On 11 Jun, 00:56, diversarts <com> wrote: 
    >
    > 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[/ref]


    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

    strawberry Guest

  7. #7

    Default Re: Ordering a list with reference to child elements

    > SELECT m1.thread_id 

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

    diversarts Guest

  8. #8

    Default Re: Ordering a list with reference to child elements

    On Jun 11, 12:28 pm, diversarts <com> wrote: 
    >
    > That still doesn't seem to work - thank you though!
    > Michael[/ref]

    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!

    strawberry Guest

  9. #9

    Default Re: Ordering a list with reference to child elements

    On 11 Jun, 12:28, diversarts <com> wrote: 
    >
    > That still doesn't seem to work - thank you though!
    > Michael[/ref]

    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?

    Captain Guest

  10. #10

    Default Re: Ordering a list with reference to child elements

    On 11 Jun, 12:48, strawberry <com> wrote:
     


    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);



    diversarts Guest

Similar Threads

  1. InDesign, XML and child elements
    By pheisholt in forum Adobe Indesign Windows
    Replies: 2
    Last Post: May 24th, 01:42 AM
  2. Ordering models in a list then saving to db
    By Matt Fletcher in forum PHP Development
    Replies: 2
    Last Post: May 11th, 11:31 PM
  3. Append child elements on client-side
    By Ray at in forum ASP
    Replies: 0
    Last Post: July 25th, 12:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139