Complex SQL query across habtm join table with ordering in rails

Question

I have three tables:

Posts
Keywordings
Keywords

Relevant fields in parens.

A Post

has_many :keywordings
has_many :keywords, :through => :keywordings

A Keywording(post_id, keyword_id)

belongs_to :post
belongs_to :keyword

A Keyword(name)

has_many :keywordings
has_many :posts, :through => :keywordings

I want to find all posts that have keywords that match any (by name) from a list, ordered by how many keywords got matched. I'm convinced this can all be done in SQL, but I'm at a loss.

If I have to do somethings in Ruby, fine, but preferably all in SQL. It does have to be fast.

Answer 1

select post, count(*) from (
select distinct ks.post_id as post, ks.keywordings_id from keywordings ks
 join keyword k on ks.keyword_id = k.keyword_id
 where k.name in (list))
group by post
order by 2 desc;

Answer 2

This assumes that you've either passed in a table parameter (SQL 2008) or created one locally:

SELECT
     KW.post_id,
     COUNT(*) AS number_matched
FROM
     @keyword_list KL
INNER JOIN Keywords K ON
     K.keyword = KL.keyword
INNER JOIN Keywordings KW ON
     KW.keyword_id = K.keyword_id
GROUP BY
     KW.post_id
ORDER BY
     number_matched DESC

When you say, ordered by # matched, I assumed that you meant descending (most matches first).

Answer 3

Returns all posts that match at least one of a given list of keywords, ordered by the number of keywords matched:

select p.*
from (
    select kw.post_id, count(*) as relevance
    from keywordings kw
    inner join keywords k on kw.keyword_id = k.id
    where k.name in ('foo', 'bar')
    group by kw.post_id) pkw 
inner join posts p on pkw.post_id = p.id
order by pkw.relevance desc;

If you just want the post IDs themselves, just use the subquery and the ORDER BY.