Related Content

2008-05-01 115 words One minute

SQL to hunt down the 5 most relevant pieces of content by tag. This schema uses uri as the primary key for content (a foreign key in the tags table).

/**
 * SQL to hunt down the 5 most relevant pieces of content by tag.  This schema
 * uses `uri` as the primary key for content (a foreign key in the tags table).
 *
 * @author Phill Sparks <me@phills.me.uk>
 * @license http://creativecommons.org/licenses/by-sa/2.0/uk/ Creative Commons Attribution-ShareAlike 2.0 UK: England & Wales
 */
SELECT uri, COUNT(tag) AS related_tag_count
FROM milk_blog_tags prop
WHERE uri <> 'posts/2008/01/17/finding_the_character_map_in_mac'
AND tag IN ('software','Character Map','Character Palette','for:lsucs','osx')
GROUP BY uri
HAVING COUNT(tag) > 0
ORDER BY related_tag_count DESC, uri DESC
LIMIT 0, 5