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.
- 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
- ColdFusion returning empty response with server-error: true
- Careful applying CF11u16, CF2016u8, CF2018u2
- Sessions don't work in Chrome but do in IE
- csrfVerifyToken does not invalidate the token
- The cf_sql_ is optional in cfqueryparam
- Cookie Expires / Max-Age 1969-12-31T23:59:59.000Z
- Burst Throttling on AWS API Gateway Explained
- How to Resolve Java HTTPS Exceptions