php - Wordpress 查询添加 INNER/LEFT JOIN。为什么或如何删除/防止 INNER/LEFT JOIN？

Question

我正在为 wordpress 中的帖子开发一个过滤器，它使用来自 postmeta 的数据:我的日期过滤器正在运行并给我这个查询:

工作查询结果:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
WHERE 1=1 AND YEAR(wp_posts.post_date)=2017 
AND MONTH(wp_posts.post_date)=03 AND wp_posts.post_type = 'projects' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' 
OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' 
OR wp_posts.post_status = 'private') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 20

When I filter by postmeta.meta_value I get an extra INNER JOIN which is causing issues:

过滤器(不完全有效):

$query->set('meta_query', array(
   array(
      'key' => 'project_cust_id',
      'value' => $project_cust_id,
      'compare' => '=' 
   )
));

查询(无效结果):

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'project_cust_id' 
AND wp_postmeta.meta_value = '12345' ) ) AND wp_posts.post_type = 'projects' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' 
OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' 
OR wp_posts.post_status = 'private') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 20

当我直接在我的数据库中查询时，我没有得到任何结果和错误，但是当我删除额外的行时:INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 或者当我删除行:LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 我确实得到了预期的结果。

EDIT: MySQL returns error: Not unique value table/alias: 'wp_postmeta'

我的问题是:

• 为什么我的代码添加了另一个 INNER JOIN？
• 如何删除/防止这种额外的 INNER JOIN 或 LEFT JOIN？

如有任何帮助，我们将不胜感激。

Answer 1

由于您在 where 子句中过滤 wp_postmeta 表中的值，LEFT JOIN 将像 INNER JOIN 一样工作，因为它只会返回值在 wp_postmeta 表中具有匹配条件的。因此，使用 LEFT JOIN 或 INNER JOIN 或两者应该没有区别。如果您希望连接像 LEFT JOIN 一样工作，则 wp_postmeta 表的任何条件都应添加到 JOIN 而不是 WHERE 子句.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 

FROM   wp_posts 

       LEFT JOIN wp_postmeta 
       ON wp_posts.ID = wp_postmeta.post_id
       AND wp_postmeta.meta_key = 'project_cust_id' 
       AND wp_postmeta.meta_value = '12345'

WHERE  1=1 AND wp_posts.post_type = 'projects' 
       AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' 
       OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' 
       OR wp_posts.post_status = 'private') 

GROUP BY wp_posts.ID 

ORDER BY wp_posts.post_date DESC LIMIT 0, 20