WordPress SQL查询如何获取分类termmeta的car_status字段值

修改方案 #

你只需要对原SQL增加termmeta表的关联查询，同时调整输出逻辑即可，完整修改后的代码如下：

global $wpdb;
$categories = $wpdb->get_results("
    SELECT DISTINCT(terms.term_id) as ID, terms.name, terms.slug, tax.description, tm.meta_value as car_status
    FROM $wpdb->posts as posts
    LEFT JOIN $wpdb->term_relationships as relationships ON posts.ID = relationships.object_ID
    LEFT JOIN $wpdb->term_taxonomy as tax ON relationships.term_taxonomy_id = tax.term_taxonomy_id
    LEFT JOIN $wpdb->terms as terms ON tax.term_id = terms.term_id
    -- 新增关联termmeta表，直接过滤目标meta_key避免数据冗余
    LEFT JOIN $wpdb->termmeta as tm ON terms.term_id = tm.term_id AND tm.meta_key = 'car_status'
    WHERE 
    posts.post_status = 'publish' AND 
    posts.post_type = 'cars' AND 
    tax.taxonomy = 'cars_category' AND 
    posts.post_author = '$user_id'
    ORDER BY terms.name ASC
");

foreach($categories as $category) : ?>
<li>
    name: <?php echo esc_html($category->name); ?>
    description: <?php echo esc_html($category->description); ?>
    status: <?php echo esc_html($category->car_status ?? '未设置'); // 新增输出car_status，兼容无meta值的场景避免报错 ?>
</li>
<?php endforeach; ?>

• 这里用LEFT JOIN关联termmeta表，是为了保证即使分类没有设置car_status字段，也不会被过滤掉，如果你只需要返回有设置该meta的分类，可以把LEFT JOIN改成INNER JOIN
• 输出时加了?? '未设置'的兼容逻辑，避免部分分类无对应meta值时出现PHP警告，你可以根据需要修改默认显示文本
• 新增了esc_html()转义输出，符合WordPress安全编码规范

内容的提问来源于stack exchange，提问作者User_FTW