[wp-trac] [WordPress Trac] #34848: Add support for updating post meta in bulk

[WordPress Trac]

#34848: Add support for updating post meta in bulk
--------------------------------+-----------------------------
 Reporter:  patrickgarman       |      Owner:
     Type:  enhancement         |     Status:  new
 Priority:  normal              |  Milestone:  Awaiting Review
Component:  Options, Meta APIs  |    Version:
 Severity:  normal              |   Keywords:
  Focuses:                      |
--------------------------------+-----------------------------
 A limiting factor in performance of inserting posts into the database is
 postmeta. I've been running a patch locally which adds functions for
 adding postmeta in one function call instead of calling add_post_meta
 multiple times over and over.

 add_post_meta creates a single SQL insert query, when adding 20 post metas
 that is 20 SQL inserts all run separately. This can be greatly improved by
 combining these into a single SQL insert.

 There is a problem where updating meta in bulk would likely be impossible
 or very painful. At the very least I have not been able to find a way to
 do this. Deleting I haven't developed a function but I imagine it would be
 fairly easy.

 {{{#!php
 /**
  * Add metadatas to a post.
  *
  * @since x.x.x
  *
  * @param int    $post_id    Post ID.
  * @param string $meta_data  Metadata as an key/value pair array
  *
  * @return bool  Was the data inserted
  */
 function add_post_metas( $post_id, $meta_data ) {
         // Make sure meta is added to the post, not a revision.
         if ( $the_post = wp_is_post_revision($post_id) )
                 $post_id = $the_post;

         return add_metadatas('post', $post_id, $meta_data);
 }

 /**
  * Add multiple metadatas for the specified object. Similar to calling
 add_metadata for each metadata individually,
  * and is only applicable for unique meta data. If a meta key already
 exists for an object it will not be stored.
  *
  * @since x.x.x
  *
  * @global wpdb $wpdb WordPress database abstraction object.
  *
  * @param string $meta_type  Type of object metadata is for (e.g.,
 comment, post, or user)
  * @param int    $object_id  ID of the object metadata is for
  * @param array  $meta_data  Metadata as an key/value pair array
  *
  * @return bool  If the metadata was stored successfully.
  */
 function add_metadatas($meta_type, $object_id, $meta_data) {
         global $wpdb;

         if ( ! $meta_type || ! is_array( $meta_data ) || ! is_numeric(
 $object_id ) ) {
                 return false;
         }

         $object_id = absint( $object_id );
         if ( ! $object_id ) {
                 return false;
         }

         $table = _get_meta_table( $meta_type );
         if ( ! $table ) {
                 return false;
         }

         $column = sanitize_key($meta_type . '_id');

         /**
          * Filter whether to add metadatas of a specific type.
          *
          * The dynamic portion of the hook, `$meta_type`, refers to the
 meta
          * object type (comment, post, or user). Returning a non-null
 value
          * will effectively short-circuit the function.
          *
          * @since x.x.x
          *
          * @param null|bool $check      Whether to allow adding metadata
 for the given type.
          * @param int       $object_id  Object ID.
          * @param string    $meta_key   Meta key.
          * @param mixed     $meta_value Meta value. Must be serializable
 if non-scalar.
          * @param bool      $unique     Whether the specified meta key
 should be unique
          *                              for the object. Optional. Default
 false.
          */
         $check = apply_filters( "add_{$meta_type}_metadatas", null,
 $object_id, $meta_data );
         if ( null !== $check )
                 return $check;

         $_meta_data = array();
         foreach( $meta_data as $key => $value ) {
                 if ( 0 == absint( $wpdb->get_var(
                                 $wpdb->prepare( "SELECT COUNT(*) FROM
 $table WHERE meta_key = %s AND $column = %d", $key, $object_id )
                 ) ) ) {
                         $key = wp_unslash( $key );
                         $value = wp_unslash( sanitize_meta( $key, $value,
 $meta_type ) );

                         $_meta_data[ $key ] = maybe_serialize( $value );

                         /**
                          * Fires immediately before meta of a specific
 type is added.
                          *
                          * The dynamic portion of the hook, `$meta_type`,
 refers to the meta
                          * object type (comment, post, or user).
                          *
                          * @since 3.1.0
                          *
                          * @param int    $object_id  Object ID.
                          * @param string $meta_key   Meta key.
                          * @param mixed  $meta_value Meta value.
                          */
                         do_action( "add_{$meta_type}_meta", $object_id,
 $key, $value );

                 }
         }

         $rows = array();
         if( ! empty( $_meta_data ) ) {
                 $sql = "INSERT INTO {$table} ({$column}, meta_key,
 meta_value) VALUES ";

                 $comma = false;
                 foreach( $_meta_data as $key => $value ) {
                         if( true == $comma ) {
                                 $sql .= ',';
                         }

                         $sql .= "({$object_id}, '{$key}', '{$value}')";
                         $comma = true;
                 }
         }

         $result = $wpdb->query( $sql );

         if ( ! $result )
                 return false;

         wp_cache_delete($object_id, $meta_type . '_meta');

         return true;
 }
 }}}

--
Ticket URL: <https://core.trac.wordpress.org/ticket/34848>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform