Changeset 13112 for trunk/src/bp-notifications/classes/class-bp-notifications-notification.php

Timestamp:

09/19/2021 03:58:09 PM (3 years ago)

Author:

imath

Message:

Improve SQL performance when updating/deleting a list of notifications

Instead of looping into a list of notifications to run an update/delete query for each notification, update/delete a list of notifications using a single query. Use primarly this change for message thread notifications and bulk notification actions.

Mainly introduces 2 new static methods to BP_Notifications_Notification:

• BP_Notifications_Notification::update_id_list() let you update a list of notifications using their notification IDs or component item IDs.
• BP_Notifications_Notification::delete_id_list() let you delete a list of notifications using their notification IDs or component item IDs.

Props oztaser

Fixes #8426

File:

• trunk/src/bp-notifications/classes/class-bp-notifications-notification.php (modified) (3 diffs)

trunk/src/bp-notifications/classes/class-bp-notifications-notification.php

@@ -912 +912 @@
 
     /**
+     * Update notifications using a list of ids/items_ids.
+     *
+     * @since 10.0.0
+     *
+     * @param string $field The name of the db field of the items to update.
+     *                      Possible values are `id` or `item_id`.
+     * @param int[]  $items The list of items to update.
+     * @param array  $data  Array of notification data to update.
+     * @param array  $where The WHERE params to use to specify the item IDs to update.
+     * @return int|false    The number of updated rows. False on error.
+     */
+    public static function update_id_list( $field, $items = array(), $data = array(), $where = array() ) {
+        global $wpdb;
+        $bp = buddypress();
+
+        $supported_fields = array( 'id', 'item_id' );
+
+        if ( false === in_array( $field, $supported_fields, true ) ) {
+            return false;
+        }
+
+        if ( ! is_array( $items ) || ! is_array( $data ) || ! is_array( $where ) ) {
+            return false;
+        }
+
+        $update_args = self::get_query_clauses( $data );
+        $where_args  = self::get_query_clauses( $where );
+
+        $fields     = array();
+        $conditions = array();
+        $values     = array();
+
+        $_items       = implode( ',', wp_parse_id_list( $items ) );
+        $conditions[] = "{$field} IN ({$_items})";
+
+        foreach ( $update_args['data'] as $field => $value ) {
+            $index  = array_search( $field, array_keys( $update_args['data'] ) );
+            $format = $update_args['format'][ $index ];
+
+            $fields[] = "{$field} = {$format}";
+            $values[] = $value;
+        }
+
+        foreach ( $where_args['data'] as $field => $value ) {
+            $index  = array_search( $field, array_keys( $where_args['data'] ) );
+            $format = $where_args['format'][ $index ];
+
+            $conditions[] = "{$field} = {$format}";
+            $values[]     = $value;
+        }
+
+        $fields     = implode( ', ', $fields );
+        $conditions = implode( ' AND ', $conditions );
+
+        /** This action is documented in bp-notifications/classes/class-bp-notifications-notification.php */
+        do_action( 'bp_notification_before_update', $update_args, $where_args );
+
+        return $wpdb->query( $wpdb->prepare( "UPDATE {$bp->notifications->table_name} SET {$fields} WHERE {$conditions}", $values ) );
+    }
+
+    /**
      * Delete notifications.
      *
@@ -917 +978 @@
      *
      * @see BP_Notifications_Notification::get() for a description of
-     *      accepted update/where arguments.
+     *      accepted where arguments.
      *
      * @param array $args Associative array of columns/values, to determine
@@ -939 +1000 @@
 
         return self::_delete( $where['data'], $where['format'] );
+    }
+
+    /**
+     * Delete notifications using a list of ids/items_ids.
+     *
+     * @since 10.0.0
+     *
+     * @param string $field The name of the db field of the items to delete.
+     *                      Possible values are `id` or `item_id`.
+     * @param int[]  $items The list of items to delete.
+     * @param array  $args  The WHERE arguments to use to specify the item IDs to delete.
+     * @return int|false    The number of deleted rows. False on error.
+     */
+    public static function delete_by_id_list( $field, $items = array(), $args = array() ) {
+        global $wpdb;
+        $bp = buddypress();
+
+        $supported_fields = array( 'id', 'item_id' );
+
+        if ( false === in_array( $field, $supported_fields, true ) ) {
+            return false;
+        }
+
+        if ( ! is_array( $items ) || ! is_array( $args ) ) {
+            return false;
+        }
+
+        $where = self::get_query_clauses( $args );
+
+        $conditions = array();
+        $values     = array();
+
+        $_items       = implode( ',', wp_parse_id_list( $items ) );
+        $conditions[] = "{$field} IN ({$_items})";
+
+        foreach ( $where['data'] as $field => $value ) {
+            $index  = array_search( $field, array_keys( $where['data'] ) );
+            $format = $where['format'][ $index ];
+
+            $conditions[] = "{$field} = {$format}";
+            $values[]     = $value;
+        }
+
+        $conditions = implode( ' AND ', $conditions );
+
+        /** This action is documented in bp-notifications/classes/class-bp-notifications-notification.php */
+        do_action( 'bp_notification_before_delete', $args );
+
+        if ( ! $values ) {
+            return $wpdb->query( "DELETE FROM {$bp->notifications->table_name} WHERE {$conditions}" );
+        }
+
+        return $wpdb->query( $wpdb->prepare( "DELETE FROM {$bp->notifications->table_name} WHERE {$conditions}", $values ) );
     }