pf » Related Posts

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.