Many to Many Related Data - 'Most Similar To'
Posted: Wed Sep 03, 2008 4:48 pm
I have a basic many to many cross reference table linking two other tables. They're very straight forward:
Getting all the images for any tag is simple, likewise getting all the tags for any image, and any combination of tags and images. I'm fine with all that.
However... I want to try and get images that are similar to an image. By similar I mean ones that share the same tags. So if an image was tagged with tags 1,2,3,4,5,6 the query would fetch all the images that have some subset of those tags, and order them by the number of tags that match. So an image that was tagged with 1,2,3,4,6 might come first, followed by one tagged with 2,3,4,5, followed by one tagged with just 4.
Every approach I've thought of so far doesn't seem to work. In my head it seems like something there should be an elegant solution to but I can't find it. Any ideas?
Code: Select all
|---------------|
| image |
|---------------|
| image_id |
| title |
| etc |
|---------------|
|---------------|
| tag |
|---------------|
| tag_id |
| tag |
| etc |
|---------------|
|---------------|
| image_tag_xref|
|---------------|
| image_id |
| tag_id |
|---------------|However... I want to try and get images that are similar to an image. By similar I mean ones that share the same tags. So if an image was tagged with tags 1,2,3,4,5,6 the query would fetch all the images that have some subset of those tags, and order them by the number of tags that match. So an image that was tagged with 1,2,3,4,6 might come first, followed by one tagged with 2,3,4,5, followed by one tagged with just 4.
Every approach I've thought of so far doesn't seem to work. In my head it seems like something there should be an elegant solution to but I can't find it. Any ideas?