0

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?

Improve this question
7
  • Keep in mind that the post meta table is optimised for queries where you already know which posts you want, queries that try to find posts using their meta are obscenely expensive, and incredibly slow. Have you considered using a taxonomy instead of post meta and raw SQL? It'll be thousands of times faster, and easier to implement
    – Tom J Nowell ♦
    May 5, 2018 at 3:09
  • Sadly, this is for a post-to-post relationship field so no real way to do this as a taxonomy. I'm trying to pull the reverse relationship. It may be expensive, especially with a LIKE, but luckily it's not a common request on this site.
    – Slam
    May 5, 2018 at 6:31
  • That's not true, use post IDs as your term slugs and you can have post to post relationships, youre just swapping meta values for term slugs, orders of magnitude faster
    – Tom J Nowell ♦
    May 5, 2018 at 11:58
  • I'm not following. I have a CPT "article" and a CPT "author," you are suggesting that editors can assign authors to articles via a taxonomy in the middle? There's ~100,000 articles and ~14,000 authors.
    – Slam
    May 5, 2018 at 22:01
  • 1
    I don't see why that would be an issue, you don't have to store everything in a single meta value, which would be bad practice anyway as it makes it very difficult to search. If you're storing structured data in post meta then even with meta queries this will be extremely difficult if not impossible. You can store just that particular piece of info in a custom taxonomy, and the rest elsewhere. If a post is related to the author X which is represented by post with ID Y, then assign it to a term named Y in a custom taxonomy
    – Tom J Nowell ♦
    May 6, 2018 at 21:56

1 Answer 1

Reset to default
0

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

Improve this answer
11
  • This... this is kinda brilliant. It's going to take me some time to understand each step completely, but I see what the goal is, and how it solves several related issues such as an articles-by-this-author archive, which I was going to create with a similarly sql-heavy postmeta search. I could also apply this to my article-to-article relationships which don't currently use a reverse-query but I can see a use case for it.
    – Slam
    May 7, 2018 at 22:57
  • Don't forget, for those situations were you need just a single 1:1 relationship, there's also post_parent too, if you're lucky enough to be able to use that, your queries will be even faster as they would rely entirely on the table indexes. For all other uses there's taxonomies. You can even duplicate data in meta and taxonomies and use the taxonomies to approximate things, like data buckets
    – Tom J Nowell ♦
    May 8, 2018 at 11:55
  • Why isn't this article-author-taxonomy bridge a plugin? This seems like a common enough use case. =)
    – Slam
    May 9, 2018 at 7:20
  • A lot of people have rather conservative ideas about what taxonomies can and can't be used for and consider this sort of stuff "abusing taxonomies", which imo is silly. It just isn't mentioned much. Having said that, there are plugins that let you add multiple authors to a post
    – Tom J Nowell ♦
    May 9, 2018 at 12:19
  • So it ends up I can't do what I want to do with post_meta searching — not without far more effort than the results will justify. I need to move forward with your idea here, but have additional questions. To create a taxonomy that shadows a cpt, I'm guessing there'd need to be some sort of hook after CPT creation. But how to move 14,000 existing cpt items into this tax? MySQL? And any issue with using cpt's slugs as term slugs? Paths would be cleaner.
    – Slam
    May 23, 2018 at 5:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.