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
Related Posts was first published on April 08, 2005.
If you like reading about tags, sql, cfquery, databases, blog, folksonomy, or tagging then you might also like:
- Make your blog better with tags
- How To Make a Tag Cloud
- Visualization of my del.icio.us tags
- Amazon Adds Tagging
- Increase Productivity with Action Tagging
- Performance of database tag schemas
- Some del.icio.us Tips
- Categories are Dead - Long live Tagging
The Fixinator Code Security Scanner for ColdFusion & CFML is an easy to use security tool that every CF developer can use. It can also easily integrate into CI for automatic scanning on every commit.