As you may have noticed I recently added tags to my blog. The main reason I implemented tags was so that I could find related posts based on mutual tags. So if two posts have similar tags, they will show up in as related.
Here's how I find the related tags:
<cfquery datasource="#ds#" name="tags"> SELECT tag FROM blogtags WHERE entryid = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.id#"> </cfquery> <cfif tags.recordcount> <cfquery datasource="#ds#" name="related"> SELECT e.title, e.id, COUNT(t.entryid) FROM blogentries AS e, blogtags AS t WHERE t.entryid <> <cfqueryparam cfsqltype="cf_sql_integer" value="#url.id#"> AND t.tag IN (<cfqueryparam list="true" value="#ValueList(tags.tag)#">) AND e.id = t.entryid GROUP BY e.title, e.id HAVING COUNT(t.entryid) > 1 ORDER BY COUNT(t.entryid) DESC LIMIT 5 </cfquery> ...display related entries </cfif>
I know, I'm using two database queries, and this could probably be done using one query, but I already need to run the
tags query to list the tags for the current entry.
The query pulls the 5 most related entries. In order for them to be related I require at least two common tags, that's what the
HAVING COUNT(t.entryid) > 1 is for.
Like this? Follow me ↯Tweet Follow @pfreitag
You might also like:
- Make your blog better with tags - October 14, 2005
- How To Make a Tag Cloud - June 24, 2005
- Visualization of my del.icio.us tags - April 24, 2006
- Amazon Adds Tagging - November 15, 2005
- Increase Productivity with Action Tagging - August 1, 2005
- Performance of database tag schemas - June 20, 2005
- Some del.icio.us Tips - June 16, 2005
- Categories are Dead - Long live Tagging - May 23, 2005