Support custom database tables to get full speed optimization

[birgire]

To get the full optimization speed, we should support the geo_query of WP_Query to read data from a custom database tables.

[baden03]

I am very interested in this as there should be a large performance improvement in doing such queries on a custom flattened meta table, such as created by the MB Custom Table extension from metabox.io.

Naturally, the next step would be to add the custom table support mentioned in this thread. Just wanted to join and collect any thoughts on how best to proceed on this. Have any ideas on this been sketched out or documented in any way since the thread was started back in 2017?

Would it be best to extend or filter the WP_Query / WP_User_Query to include the custom table, or better to write a custom SQL query? If the latter, how best to deal with implement a cache layer?

Since a custom table could hold much more meta data than just lat and lng, it would be ideal to extend the WHERE clause to allow filtering the results by other meta details.

Regardless, thank you for the great plugin, and I look forward to helping any way I can.

[birgire]

Great, @baden03 your input is greatly appreciated.

The design is still open, but in general I would prefer filtering the WP_* classes, instead of extending these classes like WP_Geo_Query extend WP_Query.

That way we can adjust e.g. the main queries if needed.

Looking at the implementation of WP_Meta_Query in WP_Query would help, I think.

One could also look at joining tables versus subqueries versus multiple queries.

There's e.g. an interesting filter posts_pre_query in WP_Query to get data from external DB, that could be helpful if custom SQL queries are needed.

But as a first try I would go with the usual table joining.

Should the plugin allow creation of such a custom table? Or should it assume existing custom tables. Or both?

I think assuming an existing table, would be a good first step. What do you think?

Creating a script to create and generate a demo table and corresponding posts, would be a good starting point, it would also help with unit testing.

[birgire]

Version 0.2.0 is out with an updated README on custom tables.

$args = array(
   	'post_type'           => 'post',    
   	'posts_per_page'      => 10,
   	'ignore_sticky_posts' => true,
   	'orderby'             => array( 'title' => 'DESC' ),		    
	'geo_query' => array(
		'table'         => 'custom_table', // Table name for the geo custom table.
		'pid_col'       => 'pid',          // Column name for the post ID data
		'lat_col'       => 'lat',          // Column name for the latitude data
		'lng_col'       => 'lng',          // Column name for the longitude data 
		'lat'           => 64.0,           // Latitude point
		'lng'           => -22.0,          // Longitude point
		'radius'        => 1,              // Find locations within a given radius (km)
		'order'         => 'DESC',         // Order by distance
		'distance_unit' => 111.045,        // Default distance unit (km per degree). Use 69.0 for statute miles per degree.
		'context'       => '\\Birgir\\Geo\\GeoQueryPostCustomTableHaversine', // Custom table implementation, you can use your own here instead.
	),
);

$query = new WP_Query( $args );