Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Is there a fast way to retrieve a specific post by a unique meta value? I could run a meta_query and then loop through the first value to get the post's ID, but I am wondering if there is any other that I am overlooking.

Is it possible to add a column to the wp_posts table? Or is there some other way to add a second unique number to each post?

To explain I need to take a WooCommerce order (which is just a post type called 'shop_order') and send it to a 3rd party and they send back a unique reference from their system, which so far I have stored as meta. Normally I would send them the post ID as the order number, because when they send their info back with the post ID as order number it is easy to then set the post meta with the additional info they send.

However, there is another plugin ( called Sequential Order numbers ) which filters the post IDs to always show a sequential number. This makes great sense from a human-readable point of view. However, I can't send this sequential order number because when it comes back to me I can't use that value to update_post_meta since it isn't post ID in the database. If I send them the actual post ID and the two sides ever have to talk in person they won't be referencing the same order ID number which would be confusing I think (much like this question).

So again to try to restate my question... is there a more efficient way to get a post by a second unique value that isn't the post ID?

share|improve this question
1  
Is there any way you can grab the Sequential Order number and stick it into a post meta field, or are they generated on the fly? –  Wyck Jan 18 '13 at 19:35
    
That's a good question. Let me go look. If it is meta, how would using the Sequential Order number be any different than using the third party's "Reference Number"? –  helgatheviking Jan 18 '13 at 19:39
    
Ok, yes it appears that the Sequential Order number is generated at wp_insert_post and is saved as post meta under the key _order_number. –  helgatheviking Jan 18 '13 at 19:41
    
Well then you can query the post id by checking that number. –  Wyck Jan 18 '13 at 20:34
    
Sorry I don't quite follow you. I don't see the difference between querying by _order_number versus querying by _reference_number ? –  helgatheviking Jan 18 '13 at 20:58
add comment

2 Answers

up vote 1 down vote accepted

I am just incredible stupid here as this can be easily done by a simple WP_Query:


new WP_Query( array( 'post_type' => 'shop_order', 'meta_key' => $meta_key, 'meta_value' => $meta_value ) )

However, I decided to compare the actual generated SQL of the get_posts() with a 'post_where' filter solution which is:


SELECT wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'engine' AND ID IN (SELECT post_id FROM wp_postmeta WHERE meta_key = "horsepower" AND meta_value = "275")  ORDER BY wp_posts.post_date DESC LIMIT 0, 5
SELECT wp_posts.* FROM wp_posts WHERE ID IN (361,327)
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (327,361)

with the actual generated SQL of the new WP_Query() solution which is:


SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1  AND wp_posts.post_type = 'engine' AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 1 AND wp_posts.post_status = 'private') AND ( (wp_postmeta.meta_key = 'horsepower' AND CAST(wp_postmeta.meta_value AS CHAR) = '275') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10
SELECT FOUND_ROWS()
SELECT wp_posts.* FROM wp_posts WHERE ID IN (361,327)
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (327,361)

So, both solutions are roughly equally efficient (the WP_Query() solution is probably slightly faster) but certainly not as efficient as human generated SQL.

share|improve this answer
    
So basically you're saying that my original thought was correct. Do a meta query and then pop off the 1 result. I'm glad I wasn't overlooking anything. Thanks for answering! –  helgatheviking Feb 18 '13 at 20:24
add comment
function get_post_by_meta_value( $meta_key, $meta_value ) {
  $post_where = function ($where) use ( $meta_key, $meta_value ) {
    global $wpdb;
    $where .= ' AND ID IN (SELECT post_id FROM ' . $wpdb->postmeta
      . ' WHERE meta_key = "' . $meta_key .'" AND meta_value = "' . $meta_value . '")';
    return $where;
  };
  add_filter( 'posts_where', $post_where );
  $args = array(
    'post_type' => 'shop_order',
    'post_status' => 'published',
    'post_per_page' => -1,
    'suppress_filters' => FALSE
  );
  $posts = get_posts( $args );
  remove_filter( 'posts_where' , $posts_where );
  return $posts;
} 

get_post_by_meta_value() returns an array of all posts of the specified post_type which have the specified meta_value for the specified meta_key which will be a 1 element array if the meta_value is unique.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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