Join and sort on 'best match'

Rails
1

Hi Everybody,

I have been breaking my head on the following problem: how to join 2
tables and sort the results on the best match.

explanation:

  • there are 3 tables, items, tags and items_tags. The items_tags table
    links items to tags.
  • I have one item which has certain tags, and I want to look up all the
    other items that have those tags as well
  • results should be sorted and presented by ‘best match’: first all the
    items that have 3 tags in common, then 2 and last 1

example:

Item 1 : news, nature, greenpeace
Item 2 : news, nature
Item 3 : news, nature, greenpeace, whale

Item 1 and Item 3 are the best match.

So far, the SQL I came up wiht looks like:

SELECT id, COUNT(items_tags.item_id) AS quantity
FROM items JOIN items_tags ON items_tags.item_id = items.id
WHERE id in (select item_id from items_tags where tag_id in (select
tag_id from items_tags where item_id=?))
GROUP BY items_tags.item_id,id
ORDER BY quantity DESC

note: the ‘?’ in the query represents the dynamic part: I have 1 item
and I want to look up matching items.

To me, this query means the following:

  • get all items that have tags, the ‘JOIN’, and count the tags, but
    only those that match on the same tags, the ‘WHERE’
  • then show them

I thought I had found the solution (my test cases worked), but I now
find cases that should be found by the query but are not.

Can anyone please help me?

Dirk

2

Hi –

On Mon, 11 Dec 2006, diddek wrote:

  • I have one item which has certain tags, and I want to look up all the
    Item 1 and Item 3 are the best match.
    note: the ‘?’ in the query represents the dynamic part: I have 1 item
    and I want to look up matching items.

To me, this query means the following:

  • get all items that have tags, the ‘JOIN’, and count the tags, but
    only those that match on the same tags, the ‘WHERE’
  • then show them

I thought I had found the solution (my test cases worked), but I now
find cases that should be found by the query but are not.

Can you show some passing and failing tests?

David

–
Q. What’s a good holiday present for the serious Rails developer?
A. RUBY FOR RAILS by David A. Black (Ruby for Rails)
aka The Ruby book for Rails developers!
Q. Where can I get Ruby/Rails on-site training, consulting, coaching?
A. Ruby Power and Light, LLC (http://www.rubypal.com)

3

Yes, but it will take some time to get a small subset of code+data.

I will get these and post them here.

Thanks,

Dirk