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