Custom Select Query: Both Category & Taxonomy?

  • Resolved travelvice

    (@travelvice)


    13 years, 1 month ago

    I’d like to query published posts for a specific category, and retrieve a custom taxonomy at the same time.

    From my understanding of the relational structure of the DB, I fear this isn’t possible with a single query, or some serious looping to clean up the duplicate results.

    The following query works. It selects published posts in the category ‘Peru’. What’s missing is pulling the name of the city, which is stored under the custom taxonomy ‘location’.

    Like I said, I believe this will either require two queries or cleaning up the duplicate records (as each post will return a row for the category and a row for the location).

    View post on imgur.com

    Likewise, there will also be a need to query for both a specific location (‘Lima’) and category (‘Peru’). Ugh.

    Thoughts?

    $query = $wpdb->prepare( "SELECT $wpdb->posts.ID, $wpdb->posts.post_title, $wpdb->posts.post_date, $wpdb->posts.comment_count
FROM $wpdb->posts
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
INNER JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
INNER JOIN $wpdb->terms ON($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id)

WHERE ($wpdb->posts.post_type = 'post'
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_parent=0
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->terms.name = 'Peru')
ORDER BY $wpdb->posts.post_date DESC;");

$resultsArray = $wpdb->get_results( $query, ARRAY_A );
Viewing 16 replies (of 16 total)
1 2
  • Thread Starter travelvice

    (@travelvice)

    13 years, 1 month ago

    THIS IS SUMMARY FOR THE SEARCHERS! 🙂

    The following pulls the posts (ordered in descending order by date) for site #2 (a travelogue) on a multisite network, where posts are in the category ‘Peru’ with a city name ‘Lima’. These posts are displayed on a Page located on site #1 (the root install of the multisite).

    The output from the example code will look something like this:
    Some Post Title — March 5, 2011 | 2 comments

    *** *** ***

    In the first example, a custom field (‘location’) is used to hold the city name.

    <?php
global $wpdb;
$wpdb->set_blog_id(2);
$wpdb->set_prefix($wpdb->base_prefix);

$query = $wpdb->prepare( "SELECT $wpdb->posts.ID, $wpdb->posts.post_title, $wpdb->posts.post_date, $wpdb->posts.comment_count
  FROM $wpdb->posts
  LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
  LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
  INNER JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
  INNER JOIN $wpdb->terms ON($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id)
  WHERE ($wpdb->posts.post_type = 'post'
  AND $wpdb->posts.post_status = 'publish'
  AND $wpdb->posts.post_parent=0
  AND $wpdb->postmeta.meta_key = 'location'
  AND $wpdb->postmeta.meta_value = 'Lima'
  AND $wpdb->term_taxonomy.taxonomy = 'category'
  AND $wpdb->terms.name = 'Peru')
  ORDER BY <code>wp3_2_posts</code>.<code>post_date</code> DESC;");

$traveloguePosts = $wpdb->get_results($query, ARRAY_A);
$blogdetails = get_blog_details(2);

foreach ( $traveloguePosts as $traveloguePost ) {
  $permaURL = str_replace($current_blog->domain . $current_blog->path, $blogdetails->domain . $blogdetails->path, get_permalink($traveloguePost[ID]));
  echo '<a href="' . $permaURL .'">' . $traveloguePost[post_title] . '</a>';
  echo ' — ' . mysql2date('F j, Y', $traveloguePost[post_date]);
  if ($traveloguePost[comment_count] >= 1) {
    echo  ' | ', $traveloguePost[comment_count], ' comment', ($traveloguePost[comment_count] > 1 ? 's' : '');
  }
  echo '<br />';
}

$wpdb->set_blog_id(1);
$wpdb->set_prefix( $wpdb->base_prefix );
?>

    *** *** ***

    In the next example, a the city name is contained within a custom taxonomy (instead of a custom field), also called ‘location’.

    <?php
global $wpdb;
$wpdb->set_blog_id(2);
$wpdb->set_prefix($wpdb->base_prefix);

$args = array(
  'orderby'       => 'post_date',
  'order'         => 'DESC',
  'post_type' 	  => 'post',
  'post_status'   => 'publish',
  'category_name' => 'Peru',
  'location'      => 'Lima'
);

$traveloguePosts = get_posts($args);
$blogdetails = get_blog_details(2);

foreach ( $traveloguePosts as $traveloguePost ) {
  $permaURL = str_replace( $current_blog->domain . $current_blog->path, $blogdetails->domain . $blogdetails->path, get_permalink( $traveloguePost->ID ) );
  echo '<a href="' . $permaURL .'">' . $traveloguePost->post_title . '</a>';
  echo ' — ' . mysql2date('F j, Y', $traveloguePost->post_date);
  if ($traveloguePost->comment_count >= 1) {
    echo  ' | ', $traveloguePost->comment_count, ' comment', ($traveloguePost->comment_count > 1 ? 's' : '');
  }
  echo '<br />';
}

$wpdb->set_blog_id(1);
$wpdb->set_prefix( $wpdb->base_prefix );
?>

    *** *** ***

    Some notes:

    • get_permalink doesn’t understand the path to network site #2, so $blogdetails is introduced for folks who don’t want to hardcode the proper domain.com/path to the site. This bloat can be removed if you add the proper path to permaURL yourself.
    • When using a custom taxonomy the taxonomy must be registered in the functions.php of site #1. See the conversation above for details.
    • If you wanted to display just the results for Peru in the second example, remove the location reference from the $args array. To then display the city, use this code inside the foreach loop:
      echo strip_tags(get_the_term_list($traveloguePost->ID, 'location'));
Viewing 16 replies (of 16 total)
1 2
  • The topic ‘Custom Select Query: Both Category & Taxonomy?’ is closed to new replies.