Related articles using Jaccard index

• Optimized for PostgreSQL
• Use plugin actsastaggable_on :tags
• For more information on how Jaccard index work see: http://en.wikipedia.org/wiki/Jaccard_index

The code...

class Article < ActiveRecord::Base
  def related limit=10
    Article.find_by_sql(%Q{
      SELECT
        a.*,
        ( SELECT array_agg(t.name)
          FROM taggings tg, tags t
          WHERE tg.taggable_id = a.id
          AND tg.tag_id = t.id
        ) as tags,
        ((SELECT COUNT(*) FROM
        ((SELECT t.name
          FROM taggings tg, tags t
          WHERE tg.taggable_id = a.id
          AND tg.tag_id = t.id
        ) INTERSECT
        (SELECT t_2.name
          FROM taggings tg_2, tags t_2
          WHERE tg_2.taggable_id = #{self.id}
          AND tg_2.tag_id = t_2.id
        )) as intersection
        )::float /
        (SELECT COUNT(*) FROM
        ((SELECT t.name
          FROM taggings tg, tags t
        WHERE tg.taggable_id = a.id
        AND tg.tag_id = t.id
        ) UNION
        (SELECT t_2.name
          FROM taggings tg_2, tags t_2
          WHERE tg_2.taggable_id = #{self.id}
          AND tg_2.tag_id = t_2.id
        )) as union_total
        )::float) as score
      FROM
        articles a
      WHERE
        a.published = 1 AND
        a.id != #{self.id}
      ORDER BY score DESC,
        a.created_at DESC
      LIMIT #{limit}      
    })
  end
end