Query postmeta based on meta_value, return array of post_id

Question

I'm trying to retrieve an array of post_id from postmeta where the meta_value matches some value.

In MySQL the query would be something like this:

SELECT post_id FROM azEw_postmeta 
WHERE meta_key='to4_related_authors' 
AND meta_value LIKE '%"203561"%';

In a WP_Query or a get_posts() function, I know the parameters will look like this:

array(
  'key' => 'to4_related_authors', 
  'value' => '"' . get_the_ID() . '"', 
  'compare' => 'LIKE'
)

I can find plenty of examples that return the posts but all I want is an array of IDs. Is there a WordPress function that returns IDs from postmeta, or do I need to do this as a $wpdb->get_results() query?

Answer 1

What you're trying to do is a post meta query, but the root problem is you're using post meta to group posts, which is what taxonomies are built for. Taxonomy tables are optimised and designed for these kinds of queries, post meta is not.

What's more, your query appears to be searching inside the meta value, suggesting that you didn't break apart the post meta into separate keys and values, and stored a structured data object instead, and are now trying to query inside serialised PHP strings

So instead, use taxonomies.

Based on your question you're trying to associate a post with another post, aka authors who are related in some way to the current post.

So instead, use a non-hierarchical taxonomy, where the slug of the term is the author post ID, and the name is the author post title.

This way, if we name our taxonomy to4_related_authors:

$authors = wp_get_object_terms( $post_id, 'to4_related_authors' );
foreach ( $authors as $author ) {
    echo $author->slug;
}

As a bonus you get a template archive, and a URL /to4_related_authors/-post-id/ that shows all the posts related to that author. Changing the parameters on registration will let you pick a prettier name.

Additionally, if you want just the IDs, use an array and a loop, you don't need a special API call for everything:

$list = [];
$authors = wp_get_object_terms( $post_id, 'to4_related_authors' );
foreach ( $authors as $author ) {
    $list[] = $author->slug; // add it to the list
}
echo implode( ', ', $list ); // output a comma separated list

Or better yet, wp_list_pluck:

$authors = wp_get_object_terms( $post_id, 'to4_related_authors' );
$list = wp_list_pluck( 'slug' );
echo implode( ', ', $list );

But keep in mind that querying post meta is extremely taxing on a server, and time consuming, especially once you move past a handful of posts. You stated that "There's ~100,000 articles and ~14,000 authors.", which means that these queries will not be scalable at all, and it would not surprise me if they took multiple seconds to execute, especially under load. Taxonomy queries would likely be 1000x faster in this scenario.

Remember, taxonomies are for anything you need to group filter or search for. They don't have to be applied to posts, just IDs, that could be user IDs, comment IDs, or any other unique numeric whole number. Likewise, their slugs don't have to be descriptive, you can use them as keys and identifiers. WP does this internally to group menu items into nav menus, they don't have to be clones of categories and tags.

Taxonomies are there for anytime you need to find anything that has X, or all the things that are Y, or the stuff that has Z, or knows A, believes B, or prefers C