自定义mysql查询-获取具有多个分类值的产品

hec6srdp 于 2021-06-25 发布在 Mysql

关注(0)|答案(2)|浏览(373)

我想编写一个定制的sql查询来获取在两个不同的分类法上具有两个特定值的产品（posts with post\u type='product'）。
基本上，我想得到的产品有一个特定的颜色，属于一个特定的类别。
两种分类法都可以在这里找到：

产品类别是该类别的分类法
颜色是颜色的分类法（这是产品属性）
这里有一个例子：我想得到所有的产品，有绿色的颜色，属于服装类
我试过不同的方法。让我陪你走过去。
如果我跑：

SELECT
wp_vge8pp_posts.post_title, wp_term2.term_id
FROM wp_vge8pp_posts
LEFT JOIN wp_vge8pp_term_relationships
ON wp_vge8pp_term_relationships.object_id = wp_vge8pp_posts.ID
LEFT JOIN wp_vge8pp_term_taxonomy wp_tax1
ON wp_vge8pp_term_relationships.term_taxonomy_id = wp_tax1.term_taxonomy_id
AND wp_tax1.taxonomy = 'product_cat'
LEFT JOIN wp_vge8pp_term_taxonomy wp_tax2
ON wp_vge8pp_term_relationships.term_taxonomy_id = wp_tax2.term_taxonomy_id
AND wp_tax2.taxonomy = 'pa_colour'
LEFT JOIN wp_vge8pp_terms wp_term1
ON wp_tax1.term_id = wp_term1.term_id
LEFT JOIN wp_vge8pp_terms wp_term2
ON wp_tax2.term_id = wp_term2.term_id
WHERE wp_vge8pp_posts.post_type = 'product'
AND wp_vge8pp_posts.post_status = 'publish'
AND wp_term2.name = 'green' /* GREEN COLOUR */

我得到的所有产品的属性都是pau colour=green，这是正确的结果。
如果我跑：

SELECT
wp_vge8pp_posts.post_title, wp_term2.term_id
FROM wp_vge8pp_posts
LEFT JOIN wp_vge8pp_term_relationships
ON wp_vge8pp_term_relationships.object_id = wp_vge8pp_posts.ID
LEFT JOIN wp_vge8pp_term_taxonomy wp_tax1
ON wp_vge8pp_term_relationships.term_taxonomy_id = wp_tax1.term_taxonomy_id
AND wp_tax1.taxonomy = 'product_cat'
LEFT JOIN wp_vge8pp_term_taxonomy wp_tax2
ON wp_vge8pp_term_relationships.term_taxonomy_id = wp_tax2.term_taxonomy_id
AND wp_tax2.taxonomy = 'pa_colour'
LEFT JOIN wp_vge8pp_terms wp_term1
ON wp_tax1.term_id = wp_term1.term_id
LEFT JOIN wp_vge8pp_terms wp_term2
ON wp_tax2.term_id = wp_term2.term_id
WHERE wp_vge8pp_posts.post_type = 'product'
AND wp_vge8pp_posts.post_status = 'publish'
AND wp_term1.name = 'dresses' /* DRESSES CATEGORY */

我得到的所有产品，有属于“礼服”类别，这是正确的结果。
但是，当我同时查询这两个分类法时，不会返回任何产品：

SELECT
wp_vge8pp_posts.post_title, wp_term2.term_id
FROM wp_vge8pp_posts
LEFT JOIN wp_vge8pp_term_relationships
ON wp_vge8pp_term_relationships.object_id = wp_vge8pp_posts.ID
LEFT JOIN wp_vge8pp_term_taxonomy wp_tax1
ON wp_vge8pp_term_relationships.term_taxonomy_id = wp_tax1.term_taxonomy_id
AND wp_tax1.taxonomy = 'product_cat'
LEFT JOIN wp_vge8pp_term_taxonomy wp_tax2
ON wp_vge8pp_term_relationships.term_taxonomy_id = wp_tax2.term_taxonomy_id
AND wp_tax2.taxonomy = 'pa_colour'
LEFT JOIN wp_vge8pp_terms wp_term1
ON wp_tax1.term_id = wp_term1.term_id
LEFT JOIN wp_vge8pp_terms wp_term2
ON wp_tax2.term_id = wp_term2.term_id
WHERE wp_vge8pp_posts.post_type = 'product'
AND wp_vge8pp_posts.post_status = 'publish'
AND wp_term2.name = 'green' /* GREEN COLOUR */
AND wp_term1.name = 'dresses' /* DRESSES CATEGORY */

如何修复查询以获得绿色且属于连衣裙类别的产品？
注：如果有用，这里是wo的db结构ocommerce:http://www.webhat.in/article/woocommerce-tutorial/how-product-attribute-are-stored-in-database/

mysql woocommerce

来源：https://stackoverflow.com/questions/50300135/woocommerce-custom-mysql-query-get-products-with-multiple-taxonomies-values

2条答案

按热度按时间

knsnq2tg1#

SELECT p.post_title
     , t2.term_id
  FROM wp_vge8pp_posts p
  LEFT 
  JOIN wp_vge8pp_term_relationships tr
    ON tr.object_id = p.ID
  LEFT 
  JOIN wp_vge8pp_term_taxonomy tx1
    ON tx1.term_taxonomy_id = tr.term_taxonomy_id 
   AND tx1.taxonomy = 'product_cat' 

  LEFT 
  JOIN wp_vge8pp_term_taxonomy tx2
    ON tx2.term_taxonomy_id = tr.term_taxonomy_id 
   AND tx2.taxonomy = 'pa_colour' 

  LEFT 
  JOIN wp_vge8pp_terms t1
    ON t1.term_id = tx1.term_id 

    -- B

  LEFT 
  JOIN wp_vge8pp_terms wp_term2 t2
    ON t2.term_id = tx2.term_id  

    -- A

 WHERE p.post_type = 'product'
   AND p.post_status = 'publish'

   AND t2.name = 'green' /* GREEN COLOUR */        -- MOVE THIS LINE TO A
   AND t1.name = 'dresses' /* DRESSES CATEGORY */  -- AND THIS LINE TO B

如果您还在苦苦挣扎，那么看看为什么我应该为我认为非常简单的sql查询提供mcve

赞(0）回复(0）举报 2021-06-25

ve7v8dk22#

在尝试了很多不同的方法之后，我找到了解决方案：

SELECT DISTINCT wp1.post_title
FROM wp_vge8pp_posts wp1
LEFT JOIN wp_vge8pp_term_relationships ON wp_vge8pp_term_relationships.object_id = wp1.ID
LEFT JOIN wp_vge8pp_term_taxonomy wp_tax1 ON wp_vge8pp_term_relationships.term_taxonomy_id = wp_tax1.term_taxonomy_id AND wp_tax1.taxonomy = 'product_cat'
LEFT JOIN wp_vge8pp_terms wp_term1 ON wp_tax1.term_id = wp_term1.term_id
WHERE wp1.post_type = 'product' AND wp1.post_status = 'publish'
AND wp_term1.name = 'dresses'

AND EXISTS (

    SELECT wp2.post_title
    FROM wp_vge8pp_posts wp2
    LEFT JOIN wp_vge8pp_term_relationships ON wp_vge8pp_term_relationships.object_id = wp2.ID
    LEFT JOIN wp_vge8pp_term_taxonomy wp_tax2 ON wp_vge8pp_term_relationships.term_taxonomy_id = wp_tax2.term_taxonomy_id AND wp_tax2.taxonomy = 'pa_colour'
    LEFT JOIN wp_vge8pp_terms wp_term2 ON wp_tax2.term_id = wp_term2.term_id
    WHERE wp_term2.name = 'green'
    AND wp1.ID = wp2.ID

)

基本上，我选择了所有的产品与类别'连衣裙'，然后缩小了寻找颜色'绿色'。
连接这两个类别的诀窍是引用表两次（给它们不同的名称），然后在最后通过post id连接它们：

wp1.ID = wp2.ID

另外，我还添加了“distinct”以确保结果中没有重复的产品。

赞(0）回复(0）举报 2021-06-25

我来回答

自定义mysql查询-获取具有多个分类值的产品

2条答案

相关问题

热门标签

最新问答