MySQL查询的排序结果（使用join）

Chris 发表于 Dev

克里斯

在WordPress安装中，我需要订购产品，以便：

售出的产品最后出现。
标记为“古董”的出售产品在标记为“复制”的出售产品之后出现。

我已经成功完成了第一项，但是我对第二项感到茫然。我没有任何错误。我的问题是我的ORDER BY语句中的所有内容都可以正常工作，除了“ wt.slug DESC”。如果将wp_term_taxonomy和wp_terms表的LEFT JOIN语句更改为INNER JOIN语句，则会得到0个结果，因此在我看来这些语句未找到我期望的结果。这是我的查询：

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
LEFT JOIN wp_postmeta stock ON ( wp_posts.ID = stock.post_id AND stock.meta_key = '_stock_status' ) 
LEFT JOIN wp_term_taxonomy wtt ON ( wp_term_relationships.term_taxonomy_id = wtt.term_taxonomy_id AND wtt.taxonomy = 'product_tag' ) 
LEFT JOIN wp_terms wt ON ( wtt.term_id = wt.term_id AND ( wt.slug = 'antique' OR wt.slug = 'reproduction' ) ) 
WHERE 1=1 
AND ( wp_term_relationships.term_taxonomy_id IN (171) ) 
AND ( ( wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog') ) ) 
AND wp_posts.post_type = 'product' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_type DESC, stock.meta_value ASC, wt.slug DESC, wp_posts.post_date

这是WordPress数据库说明，以供参考。我将不胜感激。

克里斯

感谢@mynawaz的帮助，我得以提出解决方案。我不确定这是否是最有效或最优雅的解决方案，但它的工作原理是：

SELECT SQL_CALC_FOUND_ROWS wp_posts.* , CASE wt.slug WHEN 'antique' THEN 1 WHEN 'reproduction' THEN 2 ELSE 0 END as slug_order 
FROM wp_posts 
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
LEFT JOIN wp_postmeta stock ON ( wp_posts.ID = stock.post_id AND stock.meta_key = '_stock_status' ) 
LEFT JOIN wp_term_relationships wtr ON ( wp_posts.ID = wtr.object_id AND stock.meta_value = 'outofstock' ) 
LEFT JOIN wp_term_taxonomy wtt ON ( wtr.term_taxonomy_id = wtt.term_taxonomy_id AND wtt.taxonomy = 'product_tag' ) 
LEFT JOIN wp_terms wt ON ( wtt.term_id = wt.term_id AND wt.slug IN( 'antique','reproduction' ) ) 
WHERE 1=1 
AND ( wp_term_relationships.term_taxonomy_id IN (171) ) 
AND ( ( wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog') ) ) 
AND wp_posts.post_type = 'product' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND NOT ( 
    stock.meta_value = 'outofstock' 
    AND ( CASE wt.slug WHEN 'antique' THEN 1 WHEN 'reproduction' THEN 2 ELSE 0 END ) = 0 
) 
GROUP BY wp_posts.ID, slug_order 
ORDER BY wp_posts.post_type DESC, stock.meta_value ASC, slug_order DESC, wp_posts.post_date DESC

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。