Home › Forums › General Issues › Relationship Field – Search on Product SKU (meta_query)
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?
You must be logged in to reply to this topic.
Welcome to the Advanced Custom Fields community forum.
Browse through ideas, snippets of code, questions and answers between fellow ACF users
Helping others is a great way to earn karma, gain badges and help ACF development!
We use cookies to offer you a better browsing experience, analyze site traffic and personalize content. Read about how we use cookies and how you can control them in our Cookie Policy. If you continue to use this site, you consent to our use of cookies.