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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
dblack-TKXtfPMJ4Ozk1uMJSBkQmQ@public.gmane.org
2006-Dec-11 14:43 UTC
Re: join and sort on ''best match''
Hi -- On Mon, 11 Dec 2006, diddek wrote:> > 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 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 (http://www.manning.com/black) 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) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---