php - wpdb 查询 - 即使值丢失如何显示结果？

Question

我读到，如果“$wpdb->get_results”查询中缺少数据，那么结果将完全为空。

我的查询如下:

$results = $wpdb->get_results("

SELECT 
( SELECT guid FROM ch_posts WHERE id = m.meta_value ) AS thumbnail, ID, post_title, post_name, SUBSTR(post_content, 20) AS content, post_date, t.name AS category

FROM ch_posts, ch_postmeta m

JOIN ch_term_relationships tr
JOIN ch_terms t ON t.term_id = tr.term_taxonomy_id 
JOIN ch_term_taxonomy tx ON tr.term_taxonomy_id = tx.term_taxonomy_id

WHERE post_status = 'publish'
AND post_author = $user_ID
AND post_type = 'product'
AND ch_posts.ID = m.post_id
AND m.meta_key =  '_thumbnail_id'
AND tr.object_id = ch_posts.ID
AND tx.taxonomy = 'product_cat'
");

然后

foreach ($results as $result) {     } 

由于我的帖子缺少“缩略图”记录，因此不会显示整个结果集。是否可以修改我的查询以显示找到的结果并忽略丢失的结果？或者用其他东西替换缺失的值？

我尝试过使用CASE，但没有成功。

非常欢迎任何帮助

Answer 1

第一，您的查询不完整。显示整个查询。第二，您以错误的方式获取缩略图网址。第三，对于用于获取缩略图的表，联接不应该是内部的。获取最后 20 篇带有缩略图的帖子的示例(NULL 表示无)

SELECT `posts`.`ID` AS `ID`, `posts`.`post_title` AS `title`, `posts`.`post_name` AS `slug`, `posts`.`post_content` AS `content`, `posts`.`post_excerpt` AS `excerpt`, `thumb_link` FROM `wp_posts` AS `posts`
LEFT JOIN (select `thumb`.`post_id` AS `object_id`, `thumbs_link`.`meta_value` AS `thumb_link` from `wp_postmeta` AS `thumb` 
LEFT JOIN`wp_postmeta` AS thumbs_link ON (`thumbs_link`.`post_id` = `thumb`.`meta_value`) 
AND `thumbs_link`.meta_key = "_wp_attached_file" WHERE `thumb`.`meta_key` = '_thumbnail_id' 
GROUP BY `thumb`.`post_id` ) AS `thumb`  ON `thumb`.`object_id` = `posts`.`ID`
WHERE `posts`.`post_status` = 'publish'
ORDER BY `posts`.`ID` DESC
LIMIT 20