John Davidson

Message:

I want to search the products in woocommerce in the admin only by meta OR title. How do I achieve this?

I already have added the custom meta by putting this in my functions.php:

function m_request_query( $query_vars ) {



global $typenow;

global $wpdb;

global $pagenow;



if ( 'product' === $typenow && isset( $_GET['s'] ) && 'edit.php' === $pagenow ) {

        $search_term            = esc_sql( sanitize_text_field( $_GET['s'] ) );

        $meta_key               = '_barcode';

        $post_types             = array( 'product', 'product_variation' );

        $search_results         = $wpdb->get_results(

            $wpdb->prepare(

                "SELECT DISTINCT posts.ID as product_id, posts.post_parent as parent_id FROM {$wpdb->posts} posts LEFT JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id WHERE postmeta.meta_key = '{$meta_key}' AND postmeta.meta_value LIKE %s AND posts.post_type IN ('" . implode( "','", $post_types ) . "') ORDER BY posts.post_parent ASC, posts.post_title ASC",

                '%' . $wpdb->esc_like( $search_term ) . '%'

            )

        );

        $product_ids            = wp_parse_id_list( array_merge( wp_list_pluck( $search_results, 'product_id' ), wp_list_pluck( $search_results, 'parent_id' ) ) );

        $query_vars['post__in'] = array_merge( $product_ids, $query_vars['post__in'] );

    }



    return $query_vars;

}



add_filter( 'request', 'm_request_query', 20 );

But now I want to add the product name to it, and exclude everything else. I think it searches the description.

How do I do this?

---

My Answer:
To modify the search query to include the product title and exclude other fields, you can update the SQL query in the code snippet you provided. You can modify the SQL query to search for the search term in the product title as well as the custom meta field '_barcode'. Here's how you can update the code:

php
function m_request_query( $query_vars ) {

global $typenow;
global $wpdb;
global $pagenow;

if ( 'product' === $typenow && isset( $_GET['s'] ) && 'edit.php' === $pagenow ) {
$search_term = esc_sql( sanitize_text_field( $_GET['s'] ) );
$meta_key = '_barcode';
$post_types = array( 'product', 'product_variation' );
$search_results = $wpdb->get_results(
$wpdb->prepare(
"SELECT DISTINCT posts.ID as product_id, posts.post_parent as parent_id
FROM {$wpdb->posts} posts
LEFT JOIN {$wpdb->postmeta} AS postmeta ON posts.ID = postmeta.post_id
WHERE (posts.post_title LIKE %s OR (postmeta.meta_key = '{$meta_key}' AND postmeta.meta_value LIKE %s))
AND posts.post_type IN ('" . implode( "','", $post_types ) . "')
ORDER BY posts.post_parent ASC, posts.post_title ASC",
'%' . $wpdb->esc_like( $search_term ) . '%',
'%' . $wpdb->esc_like( $search_term ) . '%'
)
);
$product_ids = wp_parse_id_list( array_merge( wp_list_pluck( $search_results, 'product_id' ), wp_list_pluck( $search_results, 'parent_id' ) );
$query_vars['post__in'] = array_merge( $product_ids, $query_vars['post__in'] );
}

return $query_vars;
}

add_filter( 'request', 'm_request_query', 20 );


In the updated code, the SQL query now searches for the search term in both the product title and the custom meta field '_barcode'. This way, the search will only include results where the search term matches either the product title or the custom meta field.