Related Posts

by Pete Freitag

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.

Related Posts

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.

Comments

foO

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 ;)

since1968

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.