Turning threaded, reverse chrono Drupal story comments into Reddit-style comments

Question

I have a Drupal site that currently displays comments in threaded, reverse chronological order. I am trying to change it to work like Reddit/Hacker News, where the threads and comments within each thread are instead ordered by their current score, based on a voting system I've added.

I've found the query used to render the comments right now. As I understand it, I need to find a way to also select the score data for each comment in this query, and then I need to sort the comments in descending order of score while still keeping them in their threads.

Here's the query:

$query = '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.picture, u.data, c.score, c.users, c.thread, c.status FROM {comments} c INNER JOIN {users} u ON c.uid = u.uid WHERE c.nid = %d  ORDER BY c.thread DESC';

The table I need to join in is called radioactivity and looks like this

@radioactivity

class | id | energy

comment | 1 | 5
comment | 2 | 8
comment | 3 | 27
comment | 4 | 13

The id column in this table syncs up with the cid column in the comments table.

I've tried this to bring in the scoring data:

$query = "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.picture, u.data, c.score, c.users, c.thread, c.status, r.energy, r.id, r.class FROM {comments} c INNER JOIN {users} u ON c.uid = u.uid INNER JOIN {radioactivity} r ON c.cid = r.id WHERE c.nid = %d AND r.class = 'comment' ORDER BY c.thread DESC";

This query only returns comments that have entries in the radioactivity table. comments that haven't received any votes don't appear in the table at all, so they're not being grabbed. How do I change this so it gets all the comments?

And then how do I change the end of the query so it orders the comments by score while still keeping them within their threads?

Right now comments are sorted based on c.thread. If a thread was structures like this:

comment
-comment
--comment
--comment
-comment
-comment

The c.thread values would be

1
1.1
1.1.1
1.1.2
1.2
1.3

I want to keep this structure, but have the comments within each level of a thread ordered based on their current score, not their c.thread value.

I know this is a lot and it's very detailed, but I thought this the best venue.

Answer 1

First of, you should not make an inner join to the radioactive table, doing that you require that there is a match, instead you could do a left join or just a join. I'm not a SQL expert so not sure what the difference between a join and left join is exactly.

About the ordering, I don't think it's possible to keep the thread and order them based on their energy. Not in SQL that is. What I would would be to get the comments as threaded, somewhere I'm sure they will be converter to an array containing the tree structure. I would use that array as basis for the ordering, since you pretty easily would be able to rearrange the different branches based on any sorting criteria you want.

Answer 2

Googletorp is correct about the outer join, I think left outer is correct for this case.

And then how do I change the end of the query so it orders the comments by score while still keeping them within their threads?

You can have multple order by clauses. I don't have your schema so can't test but something like:

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.picture,
       u.data,
       c.score,
       c.users,
       c.thread,
       c.status,
       COALESCE (r.energy,0) as energy
FROM {comments} c
INNER JOIN {users} u ON c.uid = u.uid 
LEFT OUTER JOIN {radioactivity} r ON (c.cid = r.id AND r.class = 'comment')
WHERE c.nid = %d 
ORDER BY c.thread DESC, energy DESC

Should do the trick.

Answer 3

I've been curious about Radioactivity, so I took a look at this.

In Views, the Radioactivity Sort handler provides this guidance:

• LEFT JOIN: Include all comments
• INNER JOIN: Include only comments that have radioactivity data (faster)

And in case you wanted to see it, here's the Views SQL output:

SELECT comments.cid AS cid, radioactivity_comment_1_f.energy AS radioactivity_comment_1_f_energy, COALESCE(radioactivity_comment_1_f.energy,0) AS radioactivity_comment_1_f_energy_sortkey, comments.timestamp AS comments_timestamp FROM comments comments LEFT JOIN radioactivity radioactivity_comment_1_f ON comments.cid = radioactivity_comment_1_f.id AND (radioactivity_comment_1_f.class = 'comment' AND radioactivity_comment_1_f.decay_profile = 1) ORDER BY radioactivity_comment_1_f_energy_sortkey DESC, comments_timestamp ASC