Serach for variable SKU does not work as expected

Hello, Krzysztof,

Could you please provide temporary wp-admin and FTP access? We will check what can be done to help you.
To grant WP-Admin access, please proceed to create a new user account with an administrator role through your WordPress Dashboard. Once the account is established, you may securely transmit the username and password to us via the Private Content section designated for this purpose.

For FTP access, we require the following details: FTP host, FTP username, FTP password, FTP port, and FTP encryption type. If you need assistance in creating these credentials, please reach out to your hosting provider who will guide you through the process.

Best Regards,
8Theme’s Team

Hello, Krzysztof,

Thank you for providing access. However, without FTP access, we are unable to make changes to the theme files. Therefore, we kindly ask you to add the following custom code to the functions.php file and check whether the issue has been resolved.

/**
 * Include parent products when an Elementor/XStore product search matches a variation SKU.
 */
add_filter( 'posts_search', 'xstore_child_search_parent_products_by_variation_sku', 999, 2 );
function xstore_child_search_parent_products_by_variation_sku( $search, $query ) {
	if ( is_admin() || ! ( $query instanceof WP_Query ) || ! $query->is_search() ) {
		return $search;
	}

	$search_term = $query->get( 's' );
	if ( '' === (string) $search_term || ! get_theme_mod( 'search_by_sku_et-desktop', 1 ) ) {
		return $search;
	}

	$post_type        = $query->get( 'post_type' );
	$is_product_query = ( 'product' === $post_type ) || ( is_array( $post_type ) && in_array( 'product', $post_type, true ) );
	if ( ! $is_product_query ) {
		return $search;
	}

	global $wpdb;

	$like = '%' . $wpdb->esc_like( $search_term ) . '%';

	return $wpdb->prepare(
		" AND (
			{$wpdb->posts}.post_title LIKE %s
			OR {$wpdb->posts}.post_content LIKE %s
			OR EXISTS (
				SELECT 1
				FROM {$wpdb->postmeta} product_sku
				WHERE product_sku.meta_key = '_sku'
				AND product_sku.meta_value LIKE %s
				AND product_sku.post_id = {$wpdb->posts}.ID
			)
			OR {$wpdb->posts}.ID IN (
				SELECT variation.post_parent
				FROM {$wpdb->posts} variation
				INNER JOIN {$wpdb->postmeta} variation_sku
					ON variation_sku.post_id = variation.ID
				WHERE variation.post_type = 'product_variation'
				AND variation.post_status = 'publish'
				AND variation.post_parent > 0
				AND variation_sku.meta_key = '_sku'
				AND variation_sku.meta_value LIKE %s
			)
		)",
		$like,
		$like,
		$like,
		$like
	);
}

add_filter( 'etheme_ajax_search_products_query', 'xstore_child_ajax_search_parent_products_by_variation_sku', 999 );
function xstore_child_ajax_search_parent_products_by_variation_sku( $args ) {
	if ( empty( $_REQUEST['query'] ) || empty( $args['meta_query'] ) || ! is_array( $args['meta_query'] ) ) {
		return $args;
	}

	$is_sku_query = false;
	foreach ( $args['meta_query'] as $meta_query ) {
		if ( is_array( $meta_query ) && isset( $meta_query['key'] ) && '_sku' === $meta_query['key'] ) {
			$is_sku_query = true;
			break;
		}
	}

	if ( ! $is_sku_query ) {
		return $args;
	}

	global $wpdb;

	$search_term = sanitize_text_field( wp_unslash( $_REQUEST['query'] ) );
	$like        = '%' . $wpdb->esc_like( $search_term ) . '%';

	$product_ids = $wpdb->get_col(
		$wpdb->prepare(
			"SELECT DISTINCT products.ID
			FROM {$wpdb->posts} products
			INNER JOIN {$wpdb->postmeta} product_sku
				ON product_sku.post_id = products.ID
			WHERE products.post_type = 'product'
			AND products.post_status = 'publish'
			AND product_sku.meta_key = '_sku'
			AND product_sku.meta_value LIKE %s",
			$like
		)
	);

	$variation_parent_ids = $wpdb->get_col(
		$wpdb->prepare(
			"SELECT DISTINCT variation.post_parent
			FROM {$wpdb->posts} variation
			INNER JOIN {$wpdb->posts} parent
				ON parent.ID = variation.post_parent
			INNER JOIN {$wpdb->postmeta} variation_sku
				ON variation_sku.post_id = variation.ID
			WHERE variation.post_type = 'product_variation'
			AND variation.post_status = 'publish'
			AND parent.post_type = 'product'
			AND parent.post_status = 'publish'
			AND variation_sku.meta_key = '_sku'
			AND variation_sku.meta_value LIKE %s",
			$like
		)
	);

	$product_ids = array_values( array_unique( array_map( 'absint', array_merge( $product_ids, $variation_parent_ids ) ) ) );

	$args['post_type']    = array( 'product' );
	$args['post__in']     = $product_ids ? $product_ids : array( 0 );
	$args['meta_query']   = array();
	$args['s']            = '';
	$args['orderby']      = 'post__in';
	$args['suppress_filters'] = false;

	return $args;
}

Best regards,
8Theme Team

Hello, Krzysztof,

These changes will be included in the next theme update, so there is no need to worry about the issue reappearing after the update

Best regards,
The 8Theme Team

Dear Krzysztof,

It’s great having you in our WordPress & WooCommerce community!

Every insight you share helps us refine XStore and build tools that empower thousands of online store owners worldwide.

Leave Feedback →
(takes less than a minute)

Together, we grow stronger with every release.

Topic closed.
The 8Theme Team