Friday July 23, 2004

Referrer Spam Roller does provide a Spam filtering function, however it has no global file and is therefore uniquely maintained by every blogger.

Login, then click on "Website" tab then, "Settings". Put words in the field titled:
"Ignore referering URLs that contain any of these (comma separated) words:"

You can use this to block both Spam and internal sun domains from appearing in your public referrer list.

Alternatively you can remove referrers from your public site totally by removing the following code from your theme: Login, then click on "website" tab then, "Pages". Click "Edit" button next to the page named "Weblog". Then remove the lines that look similar to this (each theme is a little different):

 <div class="menubox"><h2 class="box">referers</h2>
   #showReferers(20 25)
 </div>

The nice thing about Roller is that you can still see all your referrers when you are logged in by clicking on "Weblog" tab, then "Referrers". This way you can still analyze what Google queries and bloggers are leading people to your content. (2004-07-23 11:56:56.0) Permalink Comments [16]

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 desc

I 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]