0

The wp_postmeta table has the following columns:

meta_id, post_id, meta_key, meta_value

I insert post id, meta key and meta_value like:

$arrg = array(
    'require_login' => 'Yes',
    'restrict_by'   => 'admin',
    'access_method' => 0
);
update_post_meta( $post_id, 'restriction_settings', $arrg );

Now I want to get post_id and meta_value using meta_key. get_post_meta() function is not working here because post_id is required here. Is it possible to get all post_id and meta_value using meta_key?

1
  • Can you clarify where you're attempting to write this? In most cases, you can likely access the global $post object and then get the ID through $post->ID but if you're in the loop, you can get it with get_the_ID()
    – AuRise
    Sep 21, 2022 at 13:37

1 Answer 1

1

WordPress does not include any functionality to directly acquire a list of Post IDs and meta values by meta-key. The most direct solution is to query for all posts which have the meta key, then access their respective meta values afterwards. While it's possible to execute a SQL query to produce such a list, the need to do so may be a red flag indicating suboptimal design - you should consider other ways in which you might leverage WordPress's systems to achieve your desired outcome before settling on such a solution.

Alternate solutions include caching the collective meta values in a single transient or site option and modifying your update operation to affect both stores, or using a dedicated custom table to store the data. The "best tool for the job" is as always dependent on the specifics of your use-case.

In any case, if you need to account for large numbers of entries, you should consider incorporating pagination in order to mitigate memory and execution timeout limitations.


Post Query

The most simple solution is to query for posts which have the meta:

$query = new WP_Query( [
  'meta_key'     => 'restriction_settings',
  'meta_compare' => 'EXISTS',
] );

foreach( $query->posts as $post ) {
  $post_id  = $post->ID;
  $settings = get_post_meta( $post_id, 'restriction_settings', true );

  // ...
}

While you might think to set 'fields' => 'ids' in the query in order to improve performance by only retrieving post IDs, doing so will disable the post meta cache for the query, which may result in each subsequent call to get_post_meta() incurring an additional database hit and penalizing overall performance instead of improving it.


Custom SQL Query

The code below is only for demonstrative purposes and circumvents all of WordPress's conventional meta-data routines. This includes ignoring the actions and filters which would typically run when accessing post meta-data, as well as any interactions with the meta caches which improve the efficiency of the operation and provide interoperability with third-party caching solutions.

If you choose to go this route, it would be highly advisable to examine the implementation of get_metadata() and replicate it's hook and caching functionality in order to best ensure compatibility with both core behaviors and third-party plugins, as well as performance optimization.

function wpse409692_get_meta_post_values(
  $meta_key,
  $single   = false,
  $per_page = 100,
  $page     = 1
) {
  global $wpdb;

  $query  = "
    SELECT `post_id`, `meta_value`
    FROM {$wpdb->postmeta}
    WHERE `meta_key` = %s
  ";
  $query_values = [ $meta_key ];

  if( $per_page == 0 || $per_page < -1 || $page < 1 )
    return [];

  if( $per_page > 0 ) {
    $query  .= ' LIMIT %d, %d';
    $offset = ( $page - 1 ) * $per_page;

    array_push( $query_values, $offset, $per_page );
  }

  $results = $wpdb->get_results( 
    $wpdb->prepare( $query, $query_values ),
    ARRAY_A
  );

  if( $wpdb->last_error )
    throw new WP_Error( 'wpse409692-db-error', $wpdb->last_error );

  return array_reduce(
    $results,
    function( $post_values, $result ) {
      $post_id    = $result[ 'post_id' ];
      $meta_value = maybe_unserialize( $result['meta_value'] );

      if( $single ) {
        $post_values[ $post_id ] = $meta_value;
      }
      else {
        if( !isset( $post_values[ $post_id ] ) )
          $post_values[ $post_id ] = [ $meta_value ];
        else
          $post_values[ $post_id ][] = $meta_value;
      }

      return $post_values;
    },
    []
  );
}

Usage (retrieve the first 100 post ID/meta-value pairs):

$post_restriction_settings = wpse409692_get_meta_post_values( 'restriction_settings', true );

foreach( $post_restriction_settings as $post_id => $settings ) {
  // ...
}

Your Answer

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

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