Question 165256 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Can SQL query get category term meta (WordPress)?

I’m using this query which I found elsewhere that works fine. It basically returns a list of every category that the current author has posted in:

global $wpdb;
$categories = $wpdb->get_results("
    SELECT DISTINCT(terms.term_id) as ID, terms.name, terms.slug, tax.description
    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
    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 $category->name; ?>
    description: <?php echo $category->description; ?>
    status: <?php // status goes here ?>
</li>
<?php endforeach; ?>

But I don’t know how to modify the SQL so that it also returns the meta_key meta_value from the termmeta table:

enter image description here

Every car category in the database has a meta_key called car_status with a value stored in the termmeta table, and I’d like that value to show below the description in the foreach loop.

Tags:

2

Answers


  1. 0

    this should work for you

      global $wpdb;
    $categories = $wpdb->get_results("
        SELECT DISTINCT(terms.term_id) as ID, terms.name, terms.slug, tax.description, termmeta.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
        LEFT JOIN $wpdb->termmeta as termmeta ON terms.term_id = termmeta.term_id AND termmeta.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
    ");
    Login or Signup to reply.
  2. 0 
        <?php
    global $wpdb;
    $categories = $wpdb->get_results("
        SELECT DISTINCT(terms.term_id) as ID, terms.name, terms.slug, tax.description, termmeta.meta_key as car_status, termmeta.meta_value  
        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
        LEFT JOIN $wpdb->termmeta as termmeta  ON termmeta.term_id = terms.term_id and termmeta.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 $category->name; ?>
        description: <?php echo $category->description; ?>
        status: <?php echo $category->car_status; ?>
    </li>
    <?php endforeach; ?>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search