This deals partly with WooCommerce Products.
I have a relationship field and I want to enable the search functionality to also look at the Product SKU when searching. I would think the following would work:
/**
* Product relationship field - include searching SKU
*
* @param Array $args
* @param Array $field
* @param Integer $post_id
*
* @return $args
*/
function prefix_product_relationship_query_mods( $args, $field, $post_id ) {
$args['post_status'] = 'publish';
if( ! empty( $args['s'] ) ) {
$args['meta_query'] = array( array(
'key' => '_sku',
'value' => $args['s'],
'compare' => 'LIKE',
) );
}
return $args;
}
add_filter( 'acf/fields/relationship/query/name=field_name', 'prefix_product_relationship_query_mods', 10, 3 );
I continue to get No Results though. Looking at posts_clauses output I’m thinking _maybe_ it’s because in the Where clauses once it hits the meta_query it switches to an AND instead of an OR but I can’t say for sure. Is there something wrong with the code above? Do I need to include another hook? Can anyone replicate my issue?
You can’t search content OR a custom field by default in WP. You have to modify the query after WP has constructed it. See this explanation of how to do this https://adambalee.com/search-wordpress-by-custom-fields-without-a-plugin/
I found a possibly unstable solution but it seems to work so far without any kind of notices or errors ( as of yet ). It takes advantage of both the relationship query hook to set a specific flag and the posts_clauses hook to join the postmeta table and search on that. The SQL solution was found via Iconic WP blog post of a similar nature.
/**
* Product relationship field - include searching SKU
*
* @param Array $args
* @param Array $field
* @param Integer $post_id
*
* @return $args
*/
function prefix_product_relationship_query_mods( $args, $field, $post_id ) {
$args['post_status'] = 'publish';
if( ! empty( $args['s'] ) ) {
$args['acf_sku_search'] = true;
}
return $args;
}
add_filter( 'acf/fields/relationship/query/name=field_name', 'prefix_product_relationship_query_mods', 10, 3 );
/**
* Modify the query clauses to search on the postmeta table when asked to :)
*
* @param Array $clauses
* @param WP_Query $query
*
* @return Array $clauses
*/
function prefix_acf_relationship_search_sku( $clauses, $query ) {
global $wpdb;
if( $query->get( 'acf_sku_search', false ) ) {
$clauses['join'] = " LEFT JOIN {$wpdb->postmeta} ON {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id ";
$clauses['where'] = preg_replace(
"/\(\s*{$wpdb->posts}.post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
"({$wpdb->posts}.post_title LIKE $1) OR ({$wpdb->postmeta}.meta_key = '_sku' AND {$wpdb->postmeta}.meta_value LIKE $1)",
$clauses['where']
);
$query->set( 'acf_sku_search', false ); // Set to false just in case.
}
return $clauses;
}
add_filter( 'posts_clauses', 'prefix_acf_relationship_search_sku', 10, 2 );
This is great!
How could we pull the date the product was posted in this under the products title when you search a sku?
This works fine. But it only accepts a full SKU.
Say i have 2 products with sku:
442-2000 and 422-2001
If i search for 422 i would expect to get both these products but i dont.
How would i go about making this possible?
