时间戳

首页 > 解决方案 > MySQL 查询检索两个类别中的标签（不是 OR）[WordPress 原始 SQL]

mysql - MySQL 查询检索两个类别中的标签（不是 OR）[WordPress 原始 SQL]

问题描述

挣扎了一段时间

   SELECT DISTINCT terms2.term_id as tag_id, terms2.name as tag_name, terms2.slug as tag_slug, t2.description as tag_desc
    FROM
        wp_posts as p1
        LEFT JOIN wp_term_relationships as r1 ON p1.ID = r1.object_ID
        LEFT JOIN wp_term_taxonomy as t1 ON r1.term_taxonomy_id = t1.term_taxonomy_id
        LEFT JOIN wp_terms as terms1 ON t1.term_id = terms1.term_id,

        wp_posts as p2
        LEFT JOIN wp_term_relationships as r2 ON p2.ID = r2.object_ID
        LEFT JOIN wp_term_taxonomy as t2 ON r2.term_taxonomy_id = t2.term_taxonomy_id
        LEFT JOIN wp_terms as terms2 ON t2.term_id = terms2.term_id
    WHERE
    t1.taxonomy = 'product_cat' AND p1.post_status = 'publish' AND terms1.term_id IN (224,229) AND
    t2.taxonomy = 'product_tag' AND p2.post_status = 'publish'
        AND p1.ID = p2.ID
    ORDER by tag_name

我正在处理的线路是这一行：

t1.taxonomy = 'product_cat' AND p1.post_status = 'publish' AND terms1.term_id IN (224,229) AND

这行得通，但是该IN子句充当OR--- 因此它带回了位于term_id 224or中的标签229

我正在尝试使它只返回同时存在的标签224和229

我试过了

t1.taxonomy = 'product_cat' AND p1.post_status = 'publish' AND terms1.term_id = '224' AND t1.taxonomy = 'product_cat' AND p1.post_status = 'publish' AND terms1.term_id = '229' AND

但它只返回一个空结果......

我现在唯一的解决方案是terms1.term_id = '224'对224和 terms1.term_id = '229' 进行两次查询229，然后使用其他逻辑返回两个匹配项。

有没有办法将它组合成一个查询？

编辑：查看下面的 IN 查询结果（修改后terms1.term_id可见）

在此处输入图像描述

如您所见Gray，仅在 229 中（因此不是我试图返回的结果集的一部分）

标签： mysqlwordpress

解决方案

@BrianBruman 我假设这terms1.term_id是一个逗号分隔的值列表。如果是这种情况，您可以使用FIND_IN_SET()来确保两者224都229在逗号分隔列表中。

这是您的查询需要的样子，以便同时使用224and获取标签229：

  SELECT DISTINCT terms2.term_id as tag_id, 
      terms2.name as tag_name, 
      terms2.slug as tag_slug, 
      t2.description as tag_desc
  FROM
      wp_posts as p1
      LEFT JOIN wp_term_relationships as r1 ON p1.ID = r1.object_ID
      LEFT JOIN wp_term_taxonomy as t1 ON r1.term_taxonomy_id = t1.term_taxonomy_id
      LEFT JOIN wp_terms as terms1 ON t1.term_id = terms1.term_id
      JOIN wp_posts as p2 ON p2.ID = p1.ID
      LEFT JOIN wp_term_relationships as r2 ON p2.ID = r2.object_ID
      LEFT JOIN wp_term_taxonomy as t2 ON r2.term_taxonomy_id = t2.term_taxonomy_id
      LEFT JOIN wp_terms as terms2 ON t2.term_id = terms2.term_id
    WHERE
      t1.taxonomy = 'product_cat' 
      AND p1.post_status = 'publish' 
      AND (
              FIND_IN_SET('224', terms1.term_id) > 0 
              AND FIND_IN_SET('229', terms1.term_id) > 0
          )
      AND t2.taxonomy = 'product_tag' 
      AND p2.post_status = 'publish'         
    ORDER by tag_name

编辑：根据您的最后评论，terms1.term_id不是逗号分隔列表。在这种情况下，您需要加入另一个wp_terms. 这是您的查询需要的样子：

      SELECT DISTINCT terms2.term_id as tag_id, 
          terms2.name as tag_name, 
          terms2.slug as tag_slug, 
          t2.description as tag_desc
      FROM
          wp_posts as p1
          LEFT JOIN wp_term_relationships as r1 ON p1.ID = r1.object_ID
          LEFT JOIN wp_term_taxonomy as t1 ON r1.term_taxonomy_id = t1.term_taxonomy_id
          LEFT JOIN wp_terms as terms1 ON t1.term_id = terms1.term_id
          LEFT JOIN wp_terms as terms3 ON t1.term_id = terms3.term_id
          JOIN wp_posts as p2 ON p2.ID = p1.ID
          LEFT JOIN wp_term_relationships as r2 ON p2.ID = r2.object_ID
          LEFT JOIN wp_term_taxonomy as t2 ON r2.term_taxonomy_id = t2.term_taxonomy_id
          LEFT JOIN wp_terms as terms2 ON t2.term_id = terms2.term_id
        WHERE
          t1.taxonomy = 'product_cat' 
          AND p1.post_status = 'publish' 
          AND terms1.term_id = '224' 
          AND terms3.term_id = '229'
          AND t2.taxonomy = 'product_tag' 
          AND p2.post_status = 'publish'         
        ORDER by tag_name

最后一件事：如果您只希望结果显示 wherewp_posts有相应的记录wp_terms，并且您不应该使用on ，则应该使用常规 old ，这将大大提高查询的性能。term_id224229LEFT JOINwp_termsJOIN

希望这可以帮助！

推荐阅读