Pete Freitag Pete Freitag

Related Posts

coldfusiondatabases

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 ↯

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:

FuseGuard Web App Firewall for ColdFusion

The FuseGuard Web Application Firewall for ColdFusion & CFML is a high performance, customizable engine that blocks various attacks against your ColdFusion applications.

Comments

ahhhhhhh, thanks for posting about it, pete! appreciated. was curious if you designated the tags when you posted the blog entry, or had some kinda script/regex that automatically pulled it from the entry... or something like that.

thx again ;)
by foO on 04/11/2005 at 9:22:03 AM UTC
Pete, thanks for posting this, very helpful. This mostly works in MySQL, but needs slight tweaking. If you don't assign an alias to the COUNT() value, MySQL returns an invalid group by error. But if you do something like "COUNT(t.entryid) AS weight" and then change your ORDER BY clause to "ORDER BY weight DESC" it works fine.
by since1968 on 06/16/2005 at 3:46:05 PM UTC