Pete Freitag Pete Freitag

Related Posts

Published on April 08, 2005
By Pete Freitag
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.



tags sql cfquery databases blog folksonomy tagging

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:

Fixinator

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.


Try Fixinator

CFBreak
The weekly newsletter for the CFML Community


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