Drupal 6: "Facebook" style comment ordering (threaded, descending+ascending)

Question

Hello. Working with Drupal 6, my goal is to order a set of threaded comments similar to how Facebook outputs comments: with the 'anchor' or first comment in each thread sorted DESC, and any 'internal' thread replies sorted ASC so the newest comments are at the bottom.

Here's the SQL query from comment_render, with ordering by COMMENT_ORDER_NEWEST_FIRST:

SELECT c.cid as cid, c.pid, c.nid,
c.subject, c.comment, c.format,
c.timestamp, c.name, c.mail,
c.homepage, u.uid, u.name AS
registered_name, u.signature,
u.picture, u.data, c.thread, c.status
FROM {comments} c INNER JOIN {users} u
ON c.uid = u.uid WHERE c.nid = 141737
AND c.status = 0 ORDER BY c.thread
DESC

This returns all comments, ordered by the newest thread first:

03/
03.00/
02/
02.06/
02.05/
02.04/
02.03/
02.01/
02.00/
01/

The desired ordering in my case is this:

03/
03.00/
02/
02.00/
02.01/
02.02/
02.03/
02.04/
02.05/
02.06/
01/

Again just think of the Facebook wall and you get the idea.

Can anyone assist in enhancing the SQL query appropriately? In my case (but perhaps not in all cases) the thread depth is forcibly limited to 1 via a custom module.

One other note - in my case it only has to work under MySQL.

Answer 1

Well this query works, but again I'm a SQL noob so this probably isn't fully correct.

SELECT c.cid as cid, c.pid, c.nid, c.subject, c.comment, c.format, c.timestamp, c.name, c.mail, c.homepage, u.uid, u.name AS registered_name, u.signature, u.picture, u.data, c.thread, c.status FROM (SELECT c.cid as cid, c.pid, c.nid, c.subject, c.comment, c.format, c.timestamp, c.name, c.mail, c.homepage, u.uid, u.name AS registered_name, u.signature, u.picture, u.data, c.thread, c.status FROM comments c INNER JOIN users u ON c.uid = u.uid WHERE c.nid = 141737 AND c.status = 0 ORDER BY SUBSTRING(thread, 1, (LENGTH(thread) - 1))) c INNER JOIN users u ON c.uid = u.uid WHERE c.nid = 141737 AND c.status = 0 ORDER BY SUBSTRING(thread, 1, 2) DESC

The idea is to first query with an ordering based on the full thread (02.05, etc) and then do another SELECT on just the first two characters of the thread field. Can I get a bit of help optimizing this or is it otherwise "correct"?