Join relation with post and taxonomy using wordpress query

Posted on by

How to write a query for creating a relationship with post and taxonomy. Before writing query don’t forgot to define global $wpdb variable. $wpdb is a global variable which instantiates of the wpdb class defined in /wp-includes/wp-db.php. See below example. 

global $wpdb;

$cat_id = 14; // The category id to select
$pop = $wpdb->get_results("SELECT p.id, p.post_title, p.comment_count
FROM $wpdb->posts p
JOIN $wpdb->term_relationships tr ON (p.ID = tr.object_id)
JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
JOIN $wpdb->terms t ON (tt.term_id = t.term_id)
WHERE p.post_type='post'
AND p.post_status = 'publish'
AND tt.taxonomy = 'category'
AND t.term_id = $cat_id
ORDER BY comment_count DESC LIMIT 9");  	      

foreach ($pop as $post): setup_postdata($post); 
 echo $post->ID; // Post Id
 
the_title();  //post title

endforeach;
wp_reset_postdata();

Similarly, we provide another example for fetching records of post and post meta. 

    global $wpdb;
    $querystr = "
    SELECT $wpdb->posts.* 
    FROM $wpdb->posts, $wpdb->postmeta
    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id 
    AND $wpdb->postmeta.meta_key = 'tag' 
    AND $wpdb->postmeta.meta_value = 'email' 
    AND $wpdb->posts.post_status = 'publish' 
    AND $wpdb->posts.post_type = 'post'
    AND $wpdb->posts.post_date < NOW()
    ORDER BY $wpdb->posts.post_date DESC
 ";

 $pageposts = $wpdb->get_results($querystr, OBJECT);

This will result in an array. Print information using foreach loop.