comments plugin enhancements

[akremedy]

Greetings all - it's been a very very long time since I've done any plugin work. A testament really to the core app and all the existing plugins available, there just hasn't been any need

Unless I've missed something in Spartacus, it seems there is only one plugin available to manage display of comments on the sidebar. Please let me know if I've missed something...

I'm thinking of extending the current Comments plugin to support aggregation (option 1) and threading (option 2) of comments.

Basic aggregation would be simple - just display the last comment for any given post up to the maximum number of comments defined. The purpose is to illustrate comment activity across the blog instead of a string of comments that are relative to one post only.

A more advanced aggregation could display the last comment for any given post where the comment submitter does not equal the author. This has advantages and disadvantages in that it gets rid of the trite closing comments ("Thanks, have a nice day"), but might look like a comment submitter was left hanging with an open question, which makes the author look un-involved and irresponsible.

A super-advanced option would include comment-management in the Admin > Comments area where each comment could be flagged for display, or not, on an entry-by-entry basis. This may be a bigger pain than most folks would ever care to deal with, but it gives very granular control over what gets displayed on the home page.

Threading could be as simple as showing the last comment on any given post (up to the max comment limit) and displaying some configurable number of related comments inset a few spaces. The purpose here is simply to organize the comment string so that it is easy to distinguish from one topic to the next.

More advanced comment threading could include use of a tree model where the last comment on each post is displayed (up to the limit) and an expand/collapse button reveals some number of related comments for the particular post.

These are just some initial thoughts - any feedback, would this be a worthwhile project??

Adam

[yellowled]

These are just some initial thoughts - any feedback, would this be a worthwhile project??

Adam, I guess ultimately this is Garvin's call. Unfortunately, he's on vacation right now, but I'm sure he'll have some remarks for you when he's back.

YL

[akremedy]

Well, I did a little proof of concept around basic aggregation. I've implemented it on my production site to try it out for awhile: http://www.PigsLipstick.com. In short, this Comments plugin enhancement provides the last comment in each post where there are comments.

In this revision, I've used some SQL techniques that may not pass the most discerning dba's test, but it was quicker to implement:

Code: Select all

$q = 'SELECT
                c.body              AS comment,
                c.timestamp         AS stamp,
                c.author            AS user,
                e.title             AS subject,
                e.timestamp         AS entrystamp,
                e.id                AS entry_id,
                c.id                AS comment_id,
                c.type              AS comment_type,
                c.url               AS comment_url,
                c.title             AS comment_title,
                c.email             AS comment_email
        FROM    (SELECT * FROM '.$serendipity['dbPrefix'].'comments ORDER BY id DESC) AS c,
                '.$serendipity['dbPrefix'].'entries  AS e
       WHERE    e.id = c.entry_id
         AND    NOT (c.type = \'TRACKBACK\' AND c.author = \'' . serendipity_db_escape_string($serendipity['blogTitle']) . '\' AND c.title != \'\')
         AND    e.isdraft = \'false\'
         AND    c.status = \'approved\'
                ' . $viewtype . '
    GROUP BY    e.id
    ORDER BY    c.timestamp DESC
    LIMIT ' . $max_entries;

Of course there are configuration bits to give the option for "Linear" or "Aggregate" in the plugin config, and the FROM and GROUP BY statements are variablized to support the config options.

I think the preferred approach would be to do the same (as in results), but use a JOIN instead - though I bet for 90% of us it would never matter much anyway from a performance point of view.

I'm going to give some thought to threaded comments next...

[garvinhicking]

Hi!

Great to read about your suggestions! Especially the aggregation feature sounds useful to me. Flagging/marking comments sounds a bit much work to me, so I don'T know if many admins would really go through this hassle - whereas the aggregation could automatically show much more distributed activity.

Indeed, a solution that does the same without a subselect would be fine; currently the s9y plugins don't really often make use of subselects, mostly due to the different syntax for mysql, psotgresql etc. If a JOIN could be made that would be great. You seem eager to have a go at that, so I'd like to let you work on it until you feel confident to add it to the plugin? If you are in need of help, I'd be happy to help

Regards,
Garvin

[akremedy]

Hi Garvin,
- Well, I think I'm going to need some help on this - I've let my sql skills become very rusty I'm afraid.

Here is where I'm at - I can't seem to work out a query that does not include subselects. My mind is telling me it should be possible, for basic aggregation, to use a join, but I can't seem to figure it out.

However, when we go to the next level of complexity, which is to give a few comments per entry to achieve basic threading, I really don't think this could be done without quite a lot of complex sql (which gets into possible cross-platform compatibility issues).

This is what I've put together, which is cool and everything, but may not be the right approach fundamentally. What this query does (with hard coded limits for now) is return the last three comments for each of the last five entries grouping the comments by entry where the first entry/comment grouping displayed is the last one to be commented on, the second entry is the next-to-last commented on, and so on.

Code: Select all

$q = '     SELECT    c1.id               AS comment_id,
                     c1.entry_id         AS comment_eid,
                     c1.timestamp        AS stamp,
                     c1.author           AS user,
                     c1.email            AS comment_email,
                     c1.type             AS comment_type,
                     c1.title            AS comment_title,
                     c1.body             AS comment,
                     c1.url              AS comment_url,
                     e1.id               AS entry_id,
                     e1.timestamp        AS entrystamp,
                     e1.title            AS subject
             FROM    ' . $serendipity['dbPrefix'] . 'comments AS c1
       INNER JOIN    ' . $serendipity['dbPrefix'] . 'entries AS e1 ON e1.id = c1.entry_id
       INNER JOIN    (SELECT    c2.entry_id, MAX(c2.id) as maxid
                        FROM    ' . $serendipity['dbPrefix'] . 'comments AS c2
                    GROUP BY    c2.entry_id
                    ORDER BY    c2.id DESC
                       LIMIT    5) AS maxs ON maxs.entry_id = e1.id
            WHERE    (SELECT    COUNT(c2.id)
                        FROM    ' . $serendipity['dbPrefix'] . 'comments AS c2
                       WHERE    c2.entry_id = e1.id
                         AND    c2.id >= c1.id) <= 3
              AND    NOT (c1.type = \'TRACKBACK\' AND c1.author = \'' . serendipity_db_escape_string($serendipity['blogTitle']) . '\' AND c1.title != \'\')
              AND    e1.isdraft = \'false\'
              AND    c1.status = \'approved\'

         ORDER BY    maxs.maxid DESC, e1.id DESC, c1.id DESC';

The nice thing is that with this one query, you can have basic aggregation as well as basic threading by tuning the limits alone (the LIMIT in the second join controls the number of groups, the value in the WHERE clause controls the number of comments per entry). What isn't nice about it (beside the very convoluted sql involved) is the effort involved in parsing the array to make prettier threading where the entry topic would be displayed once with the last three comments below and offset (or whatever).

This makes me think that it might be better in the long run to use very simple queries, fetch the topic/comment header information separately from the comment details, and build the threaded comment structure in PHP instead of trying to do so much in sql.

What do you think?

By the way, I agree on not pursuing per-comment flagging for display in the comments sidebar - I can't imagine anyone would bother with level of comment moderation. So there would be linear (the way it is today), basic aggregation (show only the last comment for N number of entries based on the defined limit), and threaded (show the last N comments for the last N entries)

Thanks!
Adam

p.s.: I've left $viewtype out of the query for testing so I didn't have to change stuff outside of $q for now.

[garvinhicking]

Hi!

I think the best way is to go with some PHP glue in between.

First step, get a list of all entries that have at least one comment:

Code: Select all

SELECT COUNT(e.id) AS counter, e.id FROM serendipity_entries AS e JOIN serendipity_comments AS c ON c.entry_id = e.id ORDER BY e.timestamp GROUP BY e.id HAVING counter > 1 LIMIT 5

Now use PHP to iterate this resultset, and for each one found, you could fetch the top 3 comments?

Regards,
Garvin

[akremedy]

Two bugs already - fixed the query the ORDER BY in the second JOIN in the query above, and the other one - all the qualifications around drafts and approval, and so on would have to be done in multiple places within the query. That just adds even more to the complexity.

So, I agree - the better approach is the simpler one from a sql point of view - fetch the entries that have at least one qualifying comment, then fetch the comments for each, as needed. This makes UI development much cleaner and simplifies the whole thing tremendously.