Elasticpress: WooCommerce feature variation search on Product edit.php

[kallehauge]

Looking at WC_Admin_Post_types::product_search then they make a direct SQL Query to fetch product and variable product ids where _sku is a match (among others) and search replace the WHERE condition used to include variations.

Edit: let me just clarify. It is possible to search for _sku on variations when product_variation is part of the post_type query vars but in the default functionality of WooCommerce it will return the "parent" WP_Post when searching for _sku and a variation matches (an example could be in the admin UI on the products edit.php page).

It is currently not possible to do the same with ElasticPress (unless I have completely failed) which means that the functionality isn't 1:1.

_Sorry if this have been documented somewhere and I've missed it_

<?php
/**
 * Search by SKU or ID for products.
 *
 * @param string $where
 * @return string
 */
public function product_search( $where ) {
  global $pagenow, $wpdb, $wp;

  if ( 'edit.php' != $pagenow || ! is_search() || ! isset( $wp->query_vars['s'] ) || 'product' != $wp->query_vars['post_type'] ) {
    return $where;
  }

  $search_ids = array();
  $terms      = explode( ',', $wp->query_vars['s'] );

  foreach ( $terms as $term ) {
    if ( is_numeric( $term ) ) {
      $search_ids[] = $term;
    }

    // Attempt to get a SKU
    $sku_to_id = $wpdb->get_results( $wpdb->prepare( "SELECT ID, post_parent FROM {$wpdb->posts} LEFT JOIN {$wpdb->postmeta} ON {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id WHERE meta_key='_sku' AND meta_value LIKE %s;", '%' . $wpdb->esc_like( wc_clean( $term ) ) . '%' ) );
    $sku_to_id = array_merge( wp_list_pluck( $sku_to_id, 'ID' ), wp_list_pluck( $sku_to_id, 'post_parent' ) );

    if ( sizeof( $sku_to_id ) > 0 ) {
      $search_ids = array_merge( $search_ids, $sku_to_id );
    }
  }

  $search_ids = array_filter( array_unique( array_map( 'absint', $search_ids ) ) );

  if ( sizeof( $search_ids ) > 0 ) {
    $where = str_replace( 'AND (((', "AND ( ({$wpdb->posts}.ID IN (" . implode( ',', $search_ids ) . ")) OR ((", $where );
  }

  return $where;
}

[tlovett1]

You can definitely look up products by SKU with EP/ES. Not sure I follow the question.

[kallehauge]

@tlovett1 It is indeed possbile to search for SKU's on ex: Simple Products (haven't tried grouped) but when I'm searching for a SKU set on a product variation, then no results turn up

[ivankristianto]

@kallehauge yes I tried it, the variation SKU won't be able to search from the product admin.
I put this issue into enhancement for the future

[tlovett1]

This should work. Can someone debug?

[ivankristianto]

in product admin, it still cannot find the sku from product variation.
The query look into this search_fields:

["search_fields"]=>
  array(5) {
    [0]=>
    string(10) "post_title"
    [1]=>
    string(12) "post_content"
    [2]=>
    string(12) "post_excerpt"
    ["meta"]=>
    array(1) {
      [0]=>
      string(4) "_sku"
    }
    ["taxonomies"]=>
    array(4) {
      [0]=>
      string(8) "category"
      [1]=>
      string(8) "post_tag"
      [2]=>
      string(11) "product_tag"
      [3]=>
      string(11) "product_cat"
    }
  }

and translate to only:

{
                            "multi_match": {
                                "query": "BLUEME",
                                "type": "phrase",
                                "fields": [
                                    "post_title",
                                    "post_content",
                                    "post_excerpt",
                                    "terms.category.name",
                                    "terms.post_tag.name",
                                    "terms.product_tag.name",
                                    "terms.product_cat.name",
                                    "meta._sku.value"
                                ],
                                "boost": 4
                            }
                        },

so it only look for "meta._sku.value"

I'll debug deeper on this.

[ivankristianto]

After I take a deeper look.
EP doesn't index product variation value.
I think this could go for future release?
cc: @tlovett1

[kallehauge]

Is there a status on the thoughts of this? Ping @ivankristianto @tlovett1

[tlovett1]

This should definitely be added. @ivankristianto can you work on this?

[c9dd]

I too would like to be able to search by SKU and variation SKU, currently, too many results are returned for something that should be unique.