选择具有多个标签的行...有更好的方法吗? [英] Select rows with multiple tags... is there a better way?

问题描述

我正在尝试做一个标签系统来从数据库中选择产品.我已经读过，实现这一目标的最佳方法是通过多对多关系，因为当有很多记录时，使用 LIKE '%tag%' 会变慢.我还阅读了 this 问题在哪里匹配多个标签，你必须为每个标签做一个连接正在请求标记.

I am attempting to do a tag system for selecting products from a database. I have read that the best way to achieve this is via a many-to-many relationship as using LIKE '%tag%' is going to get slow when there are a lot of records. I have also read this question where to match on multiple tags you have to do a join for each tag being requested.

我有 3 个表:shop_products、shop_categories 和 shop_products_categories.例如，我需要能够找到同时具有鲜花"和浪漫"标签的产品.

I have 3 tables: shop_products, shop_categories and shop_products_categories. And I need to, for example, be able to find products that have both the tag "flowers" and "romance".

SELECT p.sku, p.name, p.path FROM shop_products p
  LEFT JOIN shop_products_categories pc1 ON p.sku = pc1.product_sku
  LEFT JOIN shop_categories c1           ON pc1.category_id = c1.id
  LEFT JOIN shop_products_categories pc2 ON p.sku = pc2.product_sku
  LEFT JOIN shop_categories c2           ON pc2.category_id = c2.id
WHERE c1.path = 'flowers' AND c2.path = 'romance'

这是我目前正在构建的演示查询，用于在为它编码相关的 PHP 之前检查它是否有效并且它有效.但这真的是最好的方法吗?我很难相信没有比为每个搜索的标签进行连接更好的方法来做到这一点.感谢您的任何建议.:)

This is the demo query I'm currently building to check it works before coding the relevant PHP for it and it works. But is this really the best way to do this? I find it hard to believe there isn't a better way to do this than to do a join for each tag searched. Thanks for any advice. :)

推荐答案

不需要做多个join.如果您需要匹配所有标签，您可以使用带有子查询的 IN 子句，如下所示:

No need to do multiple joins. If you need to match all tags, you can use an IN clause with a subquery like this:

select p.sku, p.name, p.path 
from shop_products p
where p.sku in (
    select pc.product_sku 
    from shop_products_categories pc 
    inner join shop_categories c on pc.category_id = c.id
    where c.path in ('flowers', 'romance')
    group by pc.product_sku
    having count(distinct c.path) = 2
)

请注意，您需要将数字 2 调整为您要匹配的唯一标签的数量.当心这是用户输入的数据并且他们输入了两次相同的标签.

Note that you will need to adjust the number 2 to be the number of unique tags you are matching on. Beware in case this is user-entered data and they enter the same tag twice.

这篇关于选择具有多个标签的行...有更好的方法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！