WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Custom Select Query: Both Category & Taxonomy? (17 posts)

  1. travelvice
    Member
    Posted 3 years 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).

    http://i.imgur.com/78r5I.png

    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 );
  2. Curtiss Grymala
    Member
    Posted 3 years ago #

    I wouldn't recommend trying to run the query directly on the database using raw SQL. Instead, use the functions that are already set up for you to retrieve your posts.

    To narrow your results according to City and Country (category), you can use something similar to the code shown in http://wordpress.pastebin.com/X7UeFS3n

    WordPress keeps a good bit of information cached, so it very well might not hit the database to retrieve parts of the information in that code; whereas the code you were using will definitely hit the database, and will be a rather expensive query with the multiple JOINs.

    To retrieve the city name when displaying all results in a specific country, use the following inside the loop:

    /* Returns an array of taxonomy objects. The "name" property is probably what you want to output on the screen */
    $cities = get_the_terms( $post->ID, 'city' );
  3. travelvice
    Member
    Posted 3 years ago #

    Fascinating, Curtiss. This is pretty neat.

    I'm experiencing the first hiccup, though:

    get_term_by( 'name', 'Lima', 'location' ) isn't working. It's returning empty (as shown by var_dump): bool(false)

    Further complicating things, it should be noted that this is a multisite, and I'm switching the $wpdb over at the start of all this.

    global $wpdb;
    $wpdb->blogid = 2;
    $wpdb->set_prefix( $wpdb->base_prefix );

    The taxonomy was registered as such:

    register_taxonomy(
      'location',
      'post',
      array(
        'label' => 'Travelogue Entry Location',
        'query_var' => true
      )
    );
  4. travelvice
    Member
    Posted 3 years ago #

    BTW - this is confirmed as working:
    $args['category_name'] = 'Peru';
    as is
    get_term_by( 'name', 'Peru', 'category' )

  5. travelvice
    Member
    Posted 3 years ago #

    Ugh, and even hard-coding the term id in there doesn't impact the results:
    $args['location'] = 6; or $args['location'] = '6';

  6. Curtiss Grymala
    Member
    Posted 3 years ago #

    Try removing the 'query_var' => true from your taxonomy registration. If left blank, the query var will default to the name of the taxonomy (in this case, "location").

    Otherwise, if you want to be able to query the taxonomy, it needs to be a string. Setting it to true will most likely kind of short-circuit the concept of being able to query the taxonomy.

    It should only be a boolean value if you set it to false, which would make the taxonomy non-queryable (as if that's a word).

  7. travelvice
    Member
    Posted 3 years ago #

    Howdy Curtiss - thanks for thinking about this with me.

    Alas, var_dump(get_term_by( 'name', 'Lima', 'location' )); still returns bool(false) with the registration adjustment.

    From the codex on this lookup:

    If $value does not exist, the return value will be false.

  8. Curtiss Grymala
    Member
    Posted 3 years ago #

    First, try the following and see what kind of output it returns:
    var_dump( get_terms( 'location', array( 'hide_empty' => false ) ) );

    That will tell you what terms (if any) are registered under the "location" taxonomy.

    If the "Lima" value isn't returned, then that means, for some reason, it doesn't exist as a "location" in your database. You may need to try editing one of the posts that should have that "location" and try setting it again.

  9. travelvice
    Member
    Posted 3 years ago #

    object(WP_Error)#51 (2) {
      ["errors"]=> array(1) {
        ["invalid_taxonomy"]=> array(1) {
          [0]=> string(16) "Invalid Taxonomy"
        }
      }
      ["error_data"]=> array(0) { }
    }

    Went back to the site and deleted the city from the posts, then deleted it from the taxonomy control. Then added it back to the posts. Same result (save for the error #... which is now #28).

    Things look fine on the database.... http://i.imgur.com/FEA0w.png

    My only guess is that it's looking in the wrong database tables for a taxonomy -- not site #2.

  10. Curtiss Grymala
    Member
    Posted 3 years ago #

    You are using the following code to switch to the right set of tables before running the query, right?

    global $wpdb;
    $oldblog = $wpdb->set_blog_id( $different_blog_id );
    /* Run your query here */
    $wpdb->set_blog_id( $oldblog );

    If so, try using wp_cache_flush() after you switch to the other blog; then run it again after you switch back.

  11. travelvice
    Member
    Posted 3 years ago #

    Still the same taxonomy result not found.

    global $wpdb;
    $wpdb->blogid = 2;
    $wpdb->set_prefix( $wpdb->base_prefix );
    wp_cache_flush();
    
    ---CODE HERE---
    
    $wpdb->blogid = 1;
    $wpdb->set_prefix( $wpdb->base_prefix );
    wp_cache_flush();

    Multisite! (shaking fist) :\

    Note: also tried it with set_blog_id instead of w/ $wpdb->blogid

  12. travelvice
    Member
    Posted 3 years ago #

    ...wait wait wait. I think I'm getting the idea of the problem here!

    Because I didn't want this taxonomy to be available across the network, I created a child theme for the travelogue that had an empty stylesheet (w/ parent import, of course) and a functions.php that contained the taxonomy registration.

    When I removed the taxonomy registration from the child theme and moved it to the parent theme's functions.php, it network-activated it, but it ALSO DISPLAYED A RESULT!!!

    array(3) {
      [0]=> object(stdClass)#38 (9) {
        ["term_id"]=> string(1) "9"
        ["name"]=> string(4) "Lima"
        ["slug"]=> string(4) "lima"
        ["term_group"]=> string(1) "0"
        ["term_taxonomy_id"]=> string(1) "9"
        ["taxonomy"]=> string(8) "location"
        ["description"]=> string(0) ""
        ["parent"]=> string(1) "0"
        ["count"]=> string(1) "2"
      }
      ...etc

    Of course the root site shows this custom taxonomy for entry and there is no data in it (as the tables aren't shared). Totally absurd.

    WTF Curtiss?! Bug??

  13. Curtiss Grymala
    Member
    Posted 3 years ago #

    Hmm. Not sure it's really a bug so much as an unexpected circumstance.

    Maybe, in order to keep it from showing up in your admin menus on the other sites, you could set up a conditional (if...else) statement in your functions.php file that uses false as the value for show_ui, show_in_nav_menus and show_tagcloud (you could just set public to false, which would cause all of those to default to false, but I'm afraid that might recreate the issue of not being able to query it) when initializing one of the other sites in the network, but keeps them set to true when you're initializing the travelogue site. That might work; as it would register the taxonomy on all sites, but should keep it from showing up anywhere but your travelogue site.

  14. travelvice
    Member
    Posted 3 years ago #

    I know this is a less than common thing to do (the work we did above, that is). I do wonder if it's just an issue with using a child theme's functions.php, or if all custom taxonomies that are trying to be accessed in this way require them to be "network-activated".

    Peculiar, that's for sure.

    BTW:
    $city = get_the_terms( $traveloguePost->ID, 'location' );

    This returns an array (as you said), but is there as less bloated way to get this single value without digging through another array? Thoughts on how to best pull the name out? I thought that maybe echoing $city[0][1] might work, but no.

    You're the best! Wow, what a trip this has been.

  15. Curtiss Grymala
    Member
    Posted 3 years ago #

    Since the child theme isn't loaded into WordPress when you're viewing a site that doesn't use that theme, the taxonomy isn't registered; so that's probably why the registration had to be placed in the parent theme's functions.php file. It's not really a matter of "network-activating" it, so much as it's an issue of making sure the taxonomy is registered on the site being viewed, probably.

    If you just want to output the city name on the page, you can use get_the_term_list() or the_terms() instead of get_the_terms().

    However, if you want to retrieve the term to use it in a query, you'll need to use get_the_terms() and access the value from the returned array. Assuming the array will always only have a single term, you could always shift or pop the item out of the returned array, and then access the term_id or name property of the item you pulled out of the array.

    Or, you could just access those properties by using $city[0]->term_id or $city[0]->name.

  16. travelvice
    Member
    Posted 3 years ago #

    Well, that certainly makes more sense as to why it was behaving like that.

    Last night I saw that I should've been using $city[0]->name, but that still spits out NULL. ...however, $city[9]->name works when the term_id of 9 has been assigned to the array name.

    Ideas on how to pop the value out of the array when it's been created as such?

    array(1) {
      [9]=> object(stdClass)#38 (10) {
        ["term_id"]=> string(1) "9"
        ["name"]=> string(4) "Lima"
        ["slug"]=> string(4) "lima"
        etc...
      }
    }
    array(1) {
      [10]=> object(stdClass)#31 (10) {
        ["term_id"]=> string(2) "10"
        ["name"]=> string(8) "Tarapoto"
        ["slug"]=> string(8) "tarapoto"
        etc...
      }
    }
  17. travelvice
    Member
    Posted 3 years 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'));

Topic Closed

This topic has been closed to new replies.

About this Topic