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

问题描述

我正在尝试创建一个标签系统，用于从数据库中选择产品.我已经读到，实现此目标的最佳方法是通过多对多关系，因为当有大量记录时，使用LIKE'％tag％'会变得很慢.我还阅读了此问题，要在多个标签上进行匹配，您必须为每个标签进行联接标签被请求.

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.而且，例如，我需要能够找到同时具有标签"flowers"和"romance"的产品.

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. :)

推荐答案

无需执行多个联接.如果需要匹配所有标记，则可以对子查询使用子句，如下所示:

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屋！