Using with PG Simple Affiliate Shop

Can’t figure this out, I see the PG database in the search options and I have it checked but Better Search does not see it.

Hi John (@healthysolutions4living),

Hi @ajay and @webberzone
I’m the author of PG Simple Affiliate Shop and I am happy to add code so that Better search will be able to return the results that @healthysolutions4living is after. I will also mention in my plugin description that it works with your plugin once I am done.

I store this information in a post meta value named “_pgeek_sas_description”. I saw that you provided an example of pulling data from ACF in one of your other support tickets (link) – can you point me to any other documentation or a simplified example? I imagine that it is fairly common to have to link in these fields but I want to do it properly. The ACF example will take me a while to unpack and if you have something available it will save a lot of time.

[ Signature deleted ]

• This reply was modified 4 years, 7 months ago by Jan Dembowski.

@peoplesgeek @healthysolutions4living

The example to search from meta keys is out here: https://gist.github.com/ajaydsouza/767215093a24bafb903a

The more simplified example is this: https://gist.github.com/ajaydsouza/767215093a24bafb903a#file-bsearch_filter_example-php

I think the second function should be:

function filter_bsearch_posts_where( $where, $search_term ) {
	global $wpdb;
	return $where . "
		AND ( $wpdb->postmeta.meta_key = '_pgeek_sas_description' AND $wpdb->postmeta.meta_value LIKE '%{$search_term}%' )
	";
}
add_filter( 'bsearch_posts_where', 'filter_bsearch_posts_where' );

Thank you,

I will try this out and let you know of the results Ajay ( @ajay )

[ Signature deleted ]

• This reply was modified 4 years, 7 months ago by Jan Dembowski.

Hi Ajay ( @ajay ),

Sorry but I am pulling my hair out a bit and any advice would be appreciated.

My code is below but it seems that the filter for ‘bsearch_posts_join’ is not being called and therefore the join clause is not getting populated. In the error log I see a “Got Here Where” but not a “Got Here Join” and the error log also shows a DB error because of the missing join (when I add the join in manually and run the resulting query directly in phpMyAdmin it returns the expected result.

WordPress database error Unknown column 'wpsas_postmeta.meta_value' in 'where clause' for query SELECT SQL_CALC_FOUND_ROWS  wpsas_posts.*,  wpsas_posts.ID as ID, 0 AS score  FROM wpsas_posts  WHERE 1=1  AND (  ((wpsas_posts.post_title LIKE '%dog%') OR (wpsas_posts.post_content LIKE '%dog%'))  OR (wpsas_posts.post_title LIKE '%dog%') OR (wpsas_posts.post_content LIKE '%dog%')  )  AND (wpsas_posts.post_status = 'publish' OR wpsas_posts.post_status = 'inherit') AND wpsas_posts.post_type IN ('post', 'page', 'pgeek_sas') 
	AND ( wpsas_postmeta.meta_value = '_pgeek_sas_description' AND wpsas_postmeta.meta_value LIKE '%dog%' )

<?php

/**
 * Joins the Post Meta table. Filters bsearch_posts_join.
 *
 * @param string $join
 * @return string
 */
function filter_pg_bsearch_posts_join( $join ) {
	global $wpdb;
	error_log("Got here Join ");
	
	return $join . "
	INNER JOIN $wpdb->postmeta ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
	";
}
add_filter( 'bsearch_posts_join', 'filter_pg_bsearch_posts_join' );

/**
 * Filters bsearch_posts_where.
 *
 * @param string $join
 * @return string
 */
function filter_pg_bsearch_posts_where( $where, $search_term ) {
	global $wpdb;
	error_log("Got here Where ");
	return $where . "
	AND ( $wpdb->postmeta.meta_value = '_pgeek_sas_description' AND $wpdb->postmeta.meta_value LIKE '%{$search_term}%' )
	";
	
}
add_filter( 'bsearch_posts_where', 'filter_pg_bsearch_posts_where' ,10 , 2);

Can you think of any reason (eg a combination of plugin setting options) that might cause this?

Many Thanks,

Brian

Hi Ajay ( @ajay ),

A little more info from my investigation – if I turn seamless mode off then the Join filter is called and that part works but now the % in the earlier SQL for my WHERE clause is replaced by a long random number (it changes with each execution)

AND (  ((wpsas_posts.post_title LIKE '{724817873c7ae117f2f65b1b11626fcd674ea0f5dbd11449827a753577203245}dog{724817873c7ae117f2f65b1b11626fcd674ea0f5dbd11449827a753577203245}') OR (wpsas_posts.post_content LIKE '{724817873c7ae117f2f65b1b11626fcd674ea0f5dbd11449827a753577203245}dog{724817873c7ae117f2f65b1b11626fcd674ea0f5dbd11449827a753577203245}'))  OR (wpsas_posts.post_title LIKE '{724817873c7ae117f2f65b1b11626fcd674ea0f5dbd11449827a753577203245}dog{724817873c7ae117f2f65b1b11626fcd674ea0f5dbd11449827a753577203245}') OR (wpsas_posts.post_content LIKE '{724817873c7ae117f2f65b1b11626fcd674ea0f5dbd11449827a753577203245}dog{724817873c7ae117f2f65b1b11626fcd674ea0f5dbd11449827a753577203245}')  )  AND (wpsas_posts.post_status = 'publish' OR wpsas_posts.post_status = 'inherit') AND wpsas_posts.post_type IN ('post', 'page', 'pgeek_sas') 
	AND ( wpsas_postmeta.meta_key = '_pgeek_sas_description' AND wpsas_postmeta.meta_value LIKE '%dog%' )

Seamless has to be on for the join to work but that causes the where clause to fail ??
Any ideas?

NB: I had a typo from other testing in my previous post wpsas_postmeta.meta_key = '_pgeek_sas_description' and not wpsas_postmeta.meta_value

Thanks, Brian

• This reply was modified 5 years ago by PeoplesGeek.
• This reply was modified 5 years ago by PeoplesGeek.

Sorry for the delay in responding. I need to relook at the seamless mode and its filters. It’s been designed to fully lock into the main query filters. Which means you can just use posts_where and posts_join to call the same bits of code as you have.

I’ll look to see what I can do to make sure there is the same filters irrespective of what happens.

I’m very confused about those random characters and what could be causing that to happen. You might need to do a careful level of debugging (var_dump) for example to see what the outputs are at the various lines