CODEWITHSUNDEEP
phpsqlwordpresssearchwoocommerce
SpectrumMike
SpectrumMike

Reputation: 21

Extend Admin product search for custom meta fields in Woocommerce

I am trying to extend the ADMIN WooCommerce Product Search to include custom fields (e.g. _sku_2 which I added via functions)

Admin Product Search

Using this as a guide, I tried to add this code to my functions, but with no luck:

// EXTEND ADMIN PRODUCT SEARCH
add_action( 'pre_get_posts', 'extend_admin_search' );
function extend_admin_search( $query ) {

 // Extend search for document post type
 $post_type = 'product';
 // Custom fields to search for
 $custom_fields = array(
        "_supplier_sku",
        "_sku_2"
     );

     if( ! is_admin() )
          return;

     if ( $query->query['post_type'] != $post_type )
    return;

     $search_term = $query->query_vars['s'];

     // Set to empty, otherwise it won't find anything
     //$query->query_vars['s'] = '';

     if ( $search_term != '' ) {
          $meta_query = array( 'relation' => 'OR' );

          foreach( $custom_fields as $custom_field ) {
                array_push( $meta_query, array(
                     'key' => $custom_field,
                     'value' => $search_term,
                     'compare' => 'LIKE'
                ));
          }

          $query->set( 'meta_query', $meta_query );
     };
}

It seems not to be editing the query results at all. Is the product search even using the default query?

Upvotes: 2

Views: 2022

Answers (1)

LoicTheAztec
LoicTheAztec

Reputation: 253919

This other way will work to extend admin product search for custom post meta keys values:

add_filter( 'posts_search', 'extend_product_search', 20, 2 );
function extend_product_search( $where, $query ) {
    global $pagenow, $wpdb;

    if ( 'edit.php' != $pagenow || ! is_search() || ! isset( $query->query_vars['s'] ) || 'product' != $query->query_vars['post_type'] ) {
        return $where;
    }
    // Here your post meta keys
    $meta_keys = array('_supplier_sku', '_sku_2');
    $meta_keys = implode("','", $meta_keys);
    // get the search value
    $term      = sanitize_text_field( $query->query_vars['s'] );
    // Light SQL Query to get the corresponding product IDs 
    $search_ids = $wpdb->get_col( "SELECT ID FROM {$wpdb->prefix}posts as p
        LEFT JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
        WHERE pm.meta_key IN ('$meta_keys') AND pm.meta_value LIKE '%$term%'" );
    // Cleaning
    $search_ids = array_filter( array_unique( array_map( 'absint', $search_ids ) ) );
    // Alter the WHERE clause in the WP_Query search
    if ( count( $search_ids ) > 0 ) {
        $where = str_replace( 'AND (((', "AND ( ({$wpdb->posts}.ID IN (" . implode( ',', $search_ids ) . ")) OR ((", $where );
    }
    return $where;
}

Code goes in function.php file of your active child theme (or active theme). Tested and work.

Upvotes: 3

Related Questions