Related Posts

April 08, 2005
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.



Related Entries

2 people found this page useful, what do you think?

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

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?