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.