Support

Account

Home Forums General Issues Relationship Field – Search on Product SKU (meta_query) Reply To: Relationship Field – Search on Product SKU (meta_query)

  • 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 );