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
comments plugin enhancements
Re: comments plugin enhancements
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.akremedy wrote:These are just some initial thoughts - any feedback, would this be a worthwhile project??
YL
Re: comments plugin enhancements
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:
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...
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;
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...
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Re: comments plugin enhancements
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
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
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
Re: comments plugin enhancements
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.
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.
- 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';
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.
Last edited by akremedy on Tue Aug 17, 2010 2:59 pm, edited 1 time in total.
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Re: comments plugin enhancements
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:
Now use PHP to iterate this resultset, and for each one found, you could fetch the top 3 comments?
Regards,
Garvin
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
Regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
Re: comments plugin enhancements
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.
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.