How can I find related tagged records using SQL, or Sphinx

I have an application which has many stories

Each story has and belongs to many tags (using acts-as-taggable-on)

Each story is also indexed by thinking sphinx

What I need is to find stories related to each other by tags and sorted in order of how many tags they share.

Given the following data:

Story #1 tagged with a,b,c,d
Story #2 tagged with a
Story #3 tagged with b,a
Story #4 tagged with d,c,b

Story.find(1).related #=> Story 4, Story 3, Story 2

...in that order

Can anybody suggest a good approach for this? I imagine there's a straightfo开发者_如何转开发rward way to do this using SQL but I'm not a SQL superman

Thanks

Bo

---

This should do it. Story.find(1).find_related_tags

---

Not sure exactly what your DB looks like but I'll assume it looks like:

stories:
  id
  content

tags:
  id
  name

story_tags:
  story_id
  tag_id

Try running a query first just to see if it gives you the intended results:

SELECT stories.id, COUNT(*) AS ordering FROM stories 
  INNER JOIN story_tags ON story_tags.story_id = stories.id 
  WHERE story_tags.tag_id IN ('id of tag a', 'id of tag b', 'id of tag c', 'id of tag d') 
  GROUP BY stories.id
  ORDER BY ordering DESC;

---

Here's what I eventually did to solve this one:

class MyModel < ActiveRecord::Base

  scope :related_to, lambda { |record|
    joins(:tags,:taggings).
    where(record.class.model_name == self.model_name ? ["#{table_name}.id != ?", record.id] : "").
    where("tags.id IN (SELECT taggings.tag_id from taggings where taggable_type = ? and taggable_id = ?)", record.class.model_name, record.id).
    order("count(DISTINCT(tags.id)) DESC").
    group("#{table_name}.id")
  }

end

Which means I can:

  @blog_post.related_to(@ad) # => returns all @blog_posts with the same tags as @ad, in order of the number of tags they share!