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

Maybe is too easy, but have you tried with `ORDER BY date, path`? — Phate01, Feb 16 '15 at 08:29
@SERG Please see my answer, I'm pretty sure it is what you want. — Jean-François Savard, Feb 17 '15 at 02:46

score 2 · Accepted Answer · edited Apr 23 '15 at 19:45

2

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.

edited Apr 23 '15 at 19:45

nnyby

4,748
10
49
105

answered Feb 17 '15 at 08:26

Adam

17,838
32
54

The only usable index is one beginning with `post_id`. `path` in an index will not help because (1) it is inside a function and (2) the mixture of DESC and ASC. – Rick James Feb 20 '15 at 00:02

score 0 · Answer 2 · answered Feb 16 '15 at 08:29

Looks like you need a combination of queries:

Create a select query where the master comments (those without a parent) are selected and ordered by descending date.
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.

score 0 · Answer 3 · answered Feb 17 '15 at 01:34

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

Materialized path sorting order by date + path

3 Answers3