I'm coding a custom Wordpress site for a customer using Woocommerce. The platform is perfect for everything, with a single issue: The client's items have custom attributes and he wants to be able to search in them.
The attributes issue itself is allowed by woocommerce. To enhance the search I've added a snippet of code to functions.php that adds extra meta to each post, with the intention of later on searching through the meta values, loosely based on the one given in this question. The code to do this goes as follows:
function wcproduct_set_attributes($id) {
$materials = get_the_terms( $id, 'pa_material');
$i = 0;
$mat_list = '';
foreach($materials as $material){
if($i == 0)
$mat_list = $mat_list . $material->name;
else
$mat_list = $mat_list . ", " . $material->name;
$i++;
}
$sizeozs = get_the_terms( $id, 'pa_sizeoz');
$i = 0;
$sizeoz_list = '';
foreach($sizeozs as $sizeoz){
if($i == 0)
$sizeoz_list = $sizeoz_list . $sizeoz->name;
else
$sizeoz_list = $sizeoz_list . ", " . $sizeoz->name;
$i++;
}
// Now update the post with its new attributes
update_post_meta($id, '_material', $mat_list);
update_post_meta($id, '_sizeoz', $sizeoz_list);
}
// After inserting post
add_action( 'save_post_product', 'wcproduct_set_attributes', 10);
Afterwards, I added another function I found through the net (here) that allows for the woocommerce search to search among custom meta information. After customizing it for my needs, I ended up with this:
/**
* Add custom join and where statements to product search query
* @param mixed $q query object
* @return void
*/
function jc_woo_search_pre_get_posts($q){
if ( is_search() ) {
add_filter( 'posts_join', 'jc_search_post_join' );
add_filter( 'posts_where', 'jc_search_post_excerpt' );
}
}
/**
* Add Custom Join Code for wp_mostmeta table
* @param string $join
* @return string
*/
function jc_search_post_join($join = ''){
global $wp_the_query;
// escape if not woocommerce searcg query
if ( empty( $wp_the_query->query_vars['wc_query'] ) || empty( $wp_the_query->query_vars['s'] ) )
return $join;
$join .= "INNER JOIN wp_postmeta AS jcmt1 ON (wp_posts.ID = jcmt1.post_id)";
return $join;
}
/**
* Add custom where statement to product search query
* @param string $where
* @return string
*/
function jc_search_post_excerpt($where = ''){
global $wp_the_query;
// escape if not woocommerce search query
if ( empty( $wp_the_query->query_vars['wc_query'] ) || empty( $wp_the_query->query_vars['s'] ) )
return $where;
$where = preg_replace("/post_title LIKE ('%[^%]+%')/", "post_title LIKE $1)
OR (jcmt1.meta_key = '_sizeoz' AND CAST(jcmt1.meta_value AS CHAR) LIKE $1)
OR (jcmt1.meta_key = '_material' AND CAST(jcmt1.meta_value AS CHAR) LIKE $1", $where);
return $where;
}
Now the system searches perfectly for a SINGLE parameter in title, content, and attributes. The problem comes when I try to search for two attributes at the same time. For example, I have a material called Titanite and a size in oz set to 5oz. If I search for Titanite, I get as a result two products with it. If I search for 5oz, I get as a result a single product with it, which is also one of the two products that appear when I search for Titanite. So far, so good.
The problem comes if I search for Titanite 5oz, since in that case I get an empty search result for some reason, even when there is an item with its material set to Titanite and its size set to 5oz. I've been staring at the code for two days and I can't find the reason. I've gone to the SQL code being injected to the search in the second function, which goes as:
AND (((wp_posts.post_title LIKE '%titanite%') OR (jcmt1.meta_key = '_sizeoz' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%titanite%') OR (jcmt1.meta_key = '_material' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%titanite%') OR (post_excerpt LIKE '%titanite%') OR (wp_posts.post_content LIKE '%titanite%')) AND ((wp_posts.post_title LIKE '%5oz%') OR (jcmt1.meta_key = '_sizeoz' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%5oz%') OR (jcmt1.meta_key = '_material' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%5oz%') OR (post_excerpt LIKE '%5oz%') OR (wp_posts.post_content LIKE '%5oz%'))) AND ( ( wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','search') ) ) AND wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
But there's no luck there either. After formatting it for easy reading, I end up with:
AND
(
((wp_posts.post_title LIKE '%titanite%')
OR (jcmt1.meta_key = '_sizeoz' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%titanite%')
OR (jcmt1.meta_key = '_material' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%titanite%')
OR (post_excerpt LIKE '%titanite%')
OR (wp_posts.post_content LIKE '%titanite%'))
AND
((wp_posts.post_title LIKE '%5oz%')
OR (jcmt1.meta_key = '_sizeoz' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%5oz%')
OR (jcmt1.meta_key = '_material' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%5oz%')
OR (post_excerpt LIKE '%5oz%')
OR (wp_posts.post_content LIKE '%5oz%'))
)
AND
( ( wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','search') ) )
AND wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
... and I can't find anything at all being wrong there. As far as I can see it's written in such a way that it should give me the results where the meta contains both Titanite and 5oz, even if on separate instances, as given by the OR value being used. Any item that's returned when you search for Titanite and 5oz should be a part of the search, so why isn't it there? Am I missing something here that's really obvious, or is the process somehow bugged?
