Thursday July 22, 2004
Sortable Interface for Roller Announcing a new sortable interface for blogs.sun.com. The default sort is the standard reverse cronological - most recent post at the top, but the more interesting sorts are by number of blog posts by author and most commented on which should indicate the hottest threads. I've noticed over several days of refining this, that hot threads are not developing very often so this column is not changing much. Perhaps a better approach than simply number of comments might be a metric like: "currently hot" = ("number of comments" - "number of days since last comment"). So that cooling threads would fade away. Perhaps the most accurate would be to assign a declining weight for number of days old to every comment and sum. Thinking about the SQL for that makes my head hurt, here is the current query for you fellow SQL jockies out there (had to push MySql 4.0.18 with a left join since sub queries and views are not available in that version yet).
select substring(w.name,1,50) as weblog_name, substring(e.title,1,50) as entry_title, u.username as author, e.pubtime as raw_date, date_format(e.pubtime,'%M %e %H:%i') as pubtime, count(c.id) as comment_count from weblogentry as e, website as w, rolleruser as u left join comment as c on c.entryid = e.id where e.publishentry = 1 and e.pubtime <= CURRENT_TIMESTAMP and w.id = e.websiteid and u.id = w.userid group by e.id order by weblog_name, raw_date descI am always at my happiest when coding SQL - can't explain it, perhaps its the feeling of buring more CPU cyles per character of code than anything else I write. I then use a sorting class designed by Matthew to order the results - wouldn't have needed it with Oracle at my disposal, but when MySql gets views in v5.0 it will do the trick.
I wanted to add the category for each post, but that would naturally invite sorting and since I am displaying two posts per blogger, that would result in two different categories per row - and present a weird sorting experience.
I welcome any comments on the design and or ideas on new metrics.
(2004-07-22 16:10:31.0)
Permalink
Comments [5]
