Materialized path sorting order by date + path

Question

I have database with comments (materialized path)(mysql). If i select all comm`s with "select * from comm where post_id=10 order by path ASC" in result i have comments from post ordered by path with older date on top and newer at bottom.

So, is there a way to select newer on top with like in disqus? if i simply "order by path DESC" - than branch is displayed upside down - it is not what i want. So i need order by date(new comm on top) and path. Thanks.!

link to bigger pic https://drive.google.com/file/d/0BwIgYhO9h960ZTl4a24wNXFFQnM/view?usp=sharing

Bigger pic

Answer 1

You just have to find the part after the first point.

Then you order DESC until the first part, and ASC after the last part.

SELECT * FROM comments 
  WHERE post_id=10
  ORDER by substring_index(path, '.', 1) DESC,
      path ASC

Note that you have an error in your attached file in the third column, inverting 9972 and 9974.

I don't know if, in this case, the MySQL optimization engine uses the index set on path to sort the result. It should be more efficient to add a column to your model.

Answer 2

Looks like you need a combination of queries:

1. Create a select query where the master comments (those without a parent) are selected and ordered by descending date.
2. Combine those results with the comments having a parent record and order those entries by ascending date.

I would suggest looking into a query containing a union. That way you can combine both result sets. Downside of this could be that your query performance is impacted when you have a lot of records. If that happens you can look into self-join queries, etc.

Answer 3

• I need a depth field that says 1 for the outer items, but >1 for the comments.
• I need a top_of_thread that contains the timestamp of the original posting.
• I need a timestamp for ordering.

Because of the lack of leading zeros in path, string comparisons won't always be right. Hence, my request for a timestamp.

ORDER BY top_of_thread DESC, -- to get the newest thread at the top
    depth=1 DESC,  -- to sort top of thread before comments
    timestamp ASC  -- oldest comment first (or DESC?)

( path LIKE '% %' ) seems to be equivalent to ( depth > 1 ) ?