Custom Select Query: Both Category & Taxonomy?

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 https://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' );

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
  )
);

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

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

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).

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.

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.

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…. https://i.imgur.com/FEA0w.png

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

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.

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

…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??

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.

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.

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.

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...
  }
}