Modify SQL search query

Anonymous

#66295

Hello,

I have a scenario in which I have to query the SQL db for geolocation data and make some computation on a query, and get results based on distance in a straight line between geolocation coordinates.

The search form has a few elements:
– Categories
– Post type
– Range
The ‘Range’ is a dropdown with a few values 10km, 15km, 20km, 25km, 50km

Now each user has a custom field with his geolocation coordinates generated from the user’s address.
Each job post in the database also has a custom field with the geolocation coordinates of the user who posted the job.

I want to modify the SQL of the search and add custom SQL to it or call a procedure with the parameters: geocoordinate of the user doing the search, and range obtained from the ‘Range’ search field. So I can do some math and get the posts within a certain radius of the searcher based on the geocoordinate fields.

What came to mind form the S&F docs was sf_edit_query_args, but since my query additions are quite comple, WP_query just doesn’t cut it, that can’t be used.

Next in line is the WordPress filter ‘posts_where’ to add additional clauses to the WHERE part of the SQL, but I would appreciate it if you can point me in the right direction! Maybe you’ve had similar requests before and there’s a better way to do this.

The main and general question here is where might I insert my code in the whole queue so that I can get and modify the search SQL query before it gets executed?

Trevor

#66305

S&F doesn’t execute queries on the posts table when the form is used. Instead, it builds cache tables in the background and it is these that are used and queried. Otherwise, executing live queries on the posts table would place a huge load on your hosting and be very slow.

It is therefore necessary for us to try to engineer geolocation and distance/proximity code into the way that the form works. It would require a geolocation data search type to be added to the Post Meta field object. As this may not be easy and require some work to achieve, this would be a feature request. I believe it has been asked for before and may well already be on our roadmap.

Anonymous

#66347

Thank you for your reply! Understandably going at this as a feature request turns out to be complicated since the new component has to be implemented following the current S&F methods.

However, I already did an example using WP ‘posts_where’ filter which catches the resulting S&F SQL query and adds to it before its execution:

//modify the Search&Find SQL query
add_filter( 'posts_where' , 'mbs_posts_where_statement' );
function mbs_posts_where_statement( $where ) {
	global $wp_query;

	//check if we're in Search context before altering the query
	if ( 345 != $wp_query->query_vars['sfid'] )
		return $where;
 	
 	//check if this is a Search query or just displaying the search widget
 	if(strpos($where, 'mbs_posts.ID IN')===FALSE)
 		return $where;

 	//alter the query
 	global $wpdb;
 	$where .= $wpdb->prepare(" AND $wpdb->posts.post_title LIKE '%%%s%%'",'elderly') ;
 
	return $where;
}

So this takes care of modifying the query.

The only thing I need to do know is find out – while inside this filter – what was selected on a dropdown – Range – in the search so I can use that selection value to change the query the way I want to. And I can’t seem to figure out how to get that data from S&F while inside the ‘posts_where’ filter, I tried with $_POST & $_REQUEST but these are not available at this point in the execution.

I would still like your input on this, maybe your developer can help. Thank you!

Trevor

#66350

This reply has been marked as private.

Anonymous

#66396

That would be great! Up until now I have figured out a solution, but still I’m looking for a more elegant one.

Here I can change the HTML output and change the _sfm__wpuf_form_id field to _mbssfm__wpuf_form_id so I can still use it’s values for myself but it won’t be included in the S&F DB query.
Of course at the end I might use some DOMDocument manipulation only on the S&F widget instead of acting on all the pages like this. This was just a quick hack.

//modify the buffer of pages
function mbs_callback($buffer) {
  if(strpos($buffer, '_sfm__wpuf_form_id')===FALSE)
  	return $buffer;

  return str_replace('_sfm__wpuf_form_id', '_mbssfm__wpuf_form_id', $buffer);
}
function mbs_buffer_start() { ob_start("mbs_callback"); }
function mbs_buffer_end() { ob_end_flush(); }

add_action('wp_head', 'mbs_buffer_start');
add_action('wp_footer', 'mbs_buffer_end');

Then I get my _mbssfm__wpuf_form_id $_REQUEST value and add it to the result of the initial S&F DB query – that’s how I understood it – so I’m filtering once more the results already filtered by S&F’s search elements.

//modify the Search&Find SQL query
add_filter( 'posts_where' , 'mbs_posts_where_statement' );
function mbs_posts_where_statement( $where ) {
	global $wp_query;

	//check if we're in Search context before altering the query
	if ( 345 != $wp_query->query_vars['sfid'] )
		return $where;
 	
 	//check if this is a Search query or just displaying the search widget
 	if(strpos($where, 'mbs_posts.ID IN')===FALSE)
 		return $where;

 	//check if the desired request field has been passed
 	if(!isset($_REQUEST['_mbssfm__wpuf_form_id']))
 		return $where;

 	//alter the query
 	global $wpdb;
 	$where .= $wpdb->prepare(" AND $wpdb->posts.post_title LIKE '%%%s%%'",$_REQUEST['_mbssfm__wpuf_form_id']) ;

	//removes the actions hooked on the '__after_loop' (post navigation)
	remove_all_actions ( '__after_loop');
 
	return $where;
}

Ofcourse here I’m not going to pass $_REQUEST data to the SQL, I’m going to do a switch with preset values. Again this was just a quick hack.

Overall this way works! But Ross, when you have time, please have a look at it and tell me if you can think of a better approach or have any recommendations.

Anonymous

#66470

So here’s the complete solution to my situation – adding extra filtering to a S&F SQL query (using a S&F input field to pass the data) after the S&F does the initial SQL query using the rest of the input components in the S&F search form.

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Modify the Search&Find SQL query using a S&F input component to pass values through.
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */

//First filter & modify the input field we're going to use so S&F will not use it for it's own query.
//I used '_search-filter-settings' but you can use any meta_field in your WP DB.
//Be sure to grab the field name from the <SELECT> element name in the form, OR add the prefix _sfm_ to the meta_key name.
add_filter('sf_input_object_pre', 'mbs_alter_snf_field', 10, 2);
function mbs_alter_snf_field($input_args, $sfid){
	//+SET S&F FIELD TO USE
	$mbs_snf_field='_sfm__search-filter-settings';

	if(!isset($input_args['name']) || $input_args['name'] != $mbs_snf_field)
		return $input_args;

	$input_args['attributes']['name']='mbs'.$mbs_snf_field;
	return $input_args;
}

//Secondly, filter & modify the S&F query after S&F did it's filtering based on the other search fields in the form.
//Make changes to the WHERE clause of that resulting query, based on the input received from $mbs_snf_field input component.
add_filter( 'posts_where' , 'mbs_posts_where_statement' );
function mbs_posts_where_statement( $where ) {
	global $wp_query;

	//+SET FORM ID TO REACT UPON
	$snf_form=345; 

	//+SET S&F FIELD TO USE
	$mbs_snf_field='_sfm__search-filter-settings';

	//check if we're in Search context before altering the query
	if ( $snf_form != $wp_query->query_vars['sfid'] ) return $where;

 	//check if this is a Search query or just displaying the search widget
 	if(strpos($where, 'mbs_posts.ID IN')===FALSE) return $where;

 	//check if the desired request field has been passed
 	if(!isset($_REQUEST['mbs'.$mbs_snf_field])) return $where;

 	//create a array and get values from it if the proper $_REQUEST isset, or default, so we only pass static data to SQL query.
 	$mbs_sql_range=array('10km'=>10,'15km'=>15,'20km'=>20,'25km'=>25,'50km'=>50);
 	$mbs_sql_range=(isset($mbs_sql_range[$_REQUEST['mbs'.$mbs_snf_field]])) ? $mbs_sql_range[$_REQUEST['mbs'.$mbs_snf_field]] : 50;

 	//alter the query
 	global $wpdb;
 	$where .= $wpdb->prepare(" AND $wpdb->posts.post_title LIKE '%%%s%%'",$mbs_sql_range) ;
 	
	//removes the actions hooked on the '__after_loop' (post navigation)
	remove_all_actions ( '__after_loop'); //* take this out if you don't need it.
 
	return $where;
}

There is only one issue remaining, the input field used to pass the data, does not retain its selection when subsequently displaying the form. And that’s normal and not a very big issue.
Ross, if you have some insight on this matter, that’ll be great!

Anonymous

#66472

Replace line
if(strpos($where, ‘mbs_posts.ID IN’)===FALSE) return $where;
with
if(strpos($where, ‘_posts.ID IN’)===FALSE) return $where;

so that the code will work regardless of WP table prefix.

An Edit option on the support forum would be great so users can edit their posts in case they forget something, or need to add extra info / make corrections.

Trevor

#66512

But, of course, that only works if the user does NOT have a database table name prefix ($wpdb->prefix), so the code in fact would need to squirt the prefix in here after fetching it from WordPress variables 😉

As to editing, I can do this anytime, and I am not sure if a timer allows it for the first xx seconds or minutes after posting for other users.

Anonymous

#66788

@trevorsf, you might have Edit privileges because you’ve got a ‘Moderator’ role, or I might not have Edit privileges because I have too few posts – that’s how I see it, but this is about the internal workings of your support forum – I wouldn’t know. The certain thing is that I cannot edit my own posts.

As to the $wpdb table prefix for the above line change, it is not necessary to put the WP table prefix since S&F always returns a query that looks like AND mbs_posts.ID IN (*some_posts_ids_here*) + some more query data, so one can always find the ‘_posts.ID IN’ string when filtering the correct S&F query with the ‘posts_where’ filter.

@Ross, when you have time, your input would be appreciated about retaining the input component value in the search form, and about the general idea, maybe you have something more elegant in mind. Thank you!
As far as I’m concerned the solution to my situation regarding S&F SQL query addition is done, I just need to get the SQL geo coordinate search done (lat/lon + aprox distance bounding box -> then ‘great circle’ distance select on remaining results, something like that) and I’ll publish the code here – maybe it helps someone in the future, but that’s outside the scope of S&F support.

Anonymous

#68943

@Ross, Hi there! Any idea on my issue in the previous reply? Many thanks!

[Author]

Posts