Using having in wp_query wordpress

Question

How can we use having keyword in WP_Query object of wordpress. Say for example if I want to have it like this:-

SELECT latitude, longitude, SQRT(
    POW(69.1 * (latitude - [startlat]), 2) +
    POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < 25 ORDER BY distance;

Latitude and Longitude are already with me so to calculate runtime distance I want to do that using pagination. Any suggestions would be recommended.

FYI, this probably won't scale very well as it has to get all of the rows from `TableName`, calculate the `distance` value, filter *all* the results in `HAVING`, and then `ORDER` the results. You'll get much higher performance [leveraging spatial extensions](http://stackoverflow.com/a/12540625/1427161). — doublesharp, Dec 01 '16 at 02:07
I do understand that this is possible by changing mysql schema but I was looking if I can have some condition used with wordpress wp query having condition that would be time saver for me now. — Chand Prakash, Dec 01 '16 at 10:21
If your goal is "easy" then you can't use a `HAVING` clause with `WP_Query`. See my answer for how to do it in two steps. — doublesharp, Dec 01 '16 at 18:40

score 0 · Answer 1 · edited May 23 '17 at 12:30

The easiest way to do this is to pass an array of post.ID values to WP_Query using the post__in parameter.

// get all the post_ids that match your spatial criteria
global $wpdb;
$sql = 'SELECT post_id FROM TableName WHERE...';
$post_ids = $wpdb->get_results($sql, ARRAY_N);

// fetch the post data using the $posts_ids
$query = WP_Query( array( 'post__in' => $post_ids ) );

I recommend coming up with a scheme using triggers and spatial indexes to perform the distance query using a bounding box and the MySQL spatial extensions. With post autosaves, history, etc this query can quickly become taxing on your server resulting in pretty terrible performance, so it doesn't scale well. Some additional details on how you might avoid this are in this other answer I wrote.

Using having in wp_query wordpress

1 Answers1