I am writing a WordPress plugin which is based on code from the plugin WP_GeoPosts. This adds a few lines to the query that collects what posts to display for a page, and sorts them by distance to a given latitude/longitude set.
My addition is to filter by distance. The SELECT statement returns the calculated distance. I need to compare that distance in the WHERE statement.
Below is my SQL which has been formatted for you.
The erroneous line is at the end of the WHERE statement, AND distance <= 25
Error: "Uknown column 'distance' in 'where clause'"
What is the correct way to evaluate the distance, but still return it in the SELECT statement?
SELECT
SQL_CALC_FOUND_ROWS wp_posts.*,
( 3959 * acos(
cos( radians(44.0519) ) *
cos( radians( latitude.meta_value ) ) *
cos( radians( longitude.meta_value ) - radians(-123.0867) ) +
sin( radians(44.0519) ) *
sin( radians( latitude.meta_value ) )
) ) AS distance,
latitude.meta_value AS latitude,
longitude.meta_value AS longitude
FROM wp_posts
INNER JOIN wp_postmeta AS latitude ON wp_posts.id = latitude.post_id
INNER JOIN wp_postmeta AS longitude ON wp_posts.id = longitude.post_id
WHERE 1 = 1
AND wp_posts.post_type = 'property'
AND (
wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private'
)
AND latitude.meta_key = "latitude"
AND longitude.meta_key = "longitude"
AND distance <= 25
ORDER BY distance ASC, wp_posts.post_date DESC
LIMIT 0, 10
You can use "HAVING" after a "WHERE", but just know that "HAVING" can't be optimized the same way has "WHERE".
SELECT 1+bar AS distance FROM foo WHERE something=true HAVING distance < 25;