MYSQL gets the closest match to several WHERE

Question

In my database, I have a table with products and another table that has tags associated with each te product.

When a user views a product page, I want to show the most "related" (or closest) product to the one he is looking at.

So let the product be tagged with 5 different tags. I would like to get prodcuts that have the same 5 tags, and then those that have 4 of the same 5 tags, and then those that have 3 of the same 5 tags, etc.

To do this, I assume that I will need to create one or more MYSQL queries, but I don’t even know where to start.

Compatibility with the same 5 tags is easy, I can just use the WHERE tag = '?' AND tag = '?' ... but how can I get another (4/5, 3/5, 2/5, 1/5)?

Any help would be greatly appreciated!

Cheers Stephen

---

edits

@Orbits: My tags are on different lines ... if I couldn’t possibly perform a text match, but this is the case. The tag string consists of (id, tag, product_id)

@ cusimar9: Different table, as indicated in the post: P

@vbence: I find it as simple as possible .. that’s it ... but I don’t have a connection table

PRODUCTS:

CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

TAGS:

CREATE TABLE `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(70) NOT NULL,
  `product_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=116 ;

Answer 1

String literals are "Tag1" and therefore should be replaced with your tags, of course.

SELECT products.id, COUNT(tags.id) AS match_level
FROM products
    LEFT JOIN tags ON tags.product_id = product.id
WHERE tags.type IN ('Tag1', 'Tag2', 'Tag3', 'Tag4', 'Tag5')
GROUP BY products.id
ORDER BY match_level DESC

, tags. . .

CREATE INDEX multimatch ON tags (type, product_id);

Answer 2

SELECT A.*
  FROM ( SELECT P.*,
                P.Tag LIKE '%a%' AS Tag1,
                P.Tag LIKE '%b%' AS Tag2,
                P.Tag LIKE '%c%' AS Tag3,
                P.Tag LIKE '%d%' AS Tag4,
                P.Tag LIKE '%e%' AS Tag5
           FROM `products` P
          WHERE P.Tag LIKE '%a%' 
             OR P.Tag LIKE '%b%' 
             OR P.Tag LIKE '%c%'
             OR P.Tag LIKE '%d%' 
             OR P.Tag LIKE '%e%'
       ) A
 ORDER BY A.Tag1+A.Tag2+A.Tag3+A.Tag4+A.Tag5 DESC

Answer 3

Simples:

SELECT related.id, count(*) as matches
FROM products related,
     tags rel_tags,
     tags this_tags
WHERE this_tags.product_id=$product_id_for_this_product
AND this_tags.type=rel_tags.type
AND rel_tags.product_id<>this_tags.product_id
AND rel_tags.product_id=related.id
GROUP BY related_id
ORDER BY count(*) DESC

Answer 4

select products.id, count(*) as matches
from products 
inner join tags on products.id = tags.product_id
inner join (
    select t.id
    from products p
    inner join tags t on p.id = t.product_id
    where p.id = $product_id
) product_tags on tags.id = product_tags.id
group by products.id
order by products.id desc