0

This is an old SQL statement found on here from way back in 2011, but its the closet to doing what I need I could find and work on. Although it does exactly what I need, displays properties within a given radius of currently displayed property, I need to add the taxonomy for the contract type in so it only shows properties, given radius, contract type.

I've tried all sorts, but cant quite figure out how to join the table for terms/term relationships to do this.

Any help would be most appreciated.

This is the code I have at the moment (Messy I know).

  SELECT DISTINCT `t`.`ID`,
         6371 * 2 * ASIN( SQRT( POWER( SIN( ( '13.727561' - `t`.`latitude` ) * pi() / 180 / 2), 2 ) + COS( '13.727561' * pi() / 180) * COS( `t`.`latitude` * pi() / 180 ) * POWER( SIN( ( '100.581708' - `t`.`longitude` ) * pi() / 180 / 2 ), 2 ) ) ) AS `distance` 
    FROM (
    SELECT `wp_posts`.`ID`,
    MAX(CASE WHEN ``.`meta_key` = '_property_longitude' THEN `wp_postmeta`.`meta_value` END ) AS `longitude`,
    MAX(CASE WHEN `wp_postmeta`.`meta_key` = '_property_latitude' THEN `wp_postmeta`.`meta_value` END ) AS `latitude`
    FROM `wp_term_taxonomy`, `wp_posts` 
    LEFT JOIN `wp_postmeta` ON ( `wp_posts`.`ID` = `wp_postmeta`.`post_id` )
    WHERE `wp_posts`.`post_status` = 'publish'
    AND `wp_term_taxonomy`.`term_id` = '36'
    AND `wp_posts`.`post_type` = 'dt_properties' 
    AND `wp_postmeta`.`post_id` != '2553' 
    GROUP BY `wp_posts`.`ID` 
    HAVING `longitude` BETWEEN '100.485824158' AND '100.677591842' AND `latitude` BETWEEN '13.6344160725' AND '13.8207059275') AS `t`
    HAVING distance < 6.427
        ORDER BY distance ASC;
  • Have you checked out the native meta_query and tax_query parameters in WP_Query. There should be no need for a custom SQL query. – Pieter Goosen May 5 '15 at 12:08
  • This is going to be a very expensive query to run – Tom J Nowell May 5 '15 at 12:27
  • Thanks for reply Pieter... To be honest, I'm rather new to WP and all its functions/parameters are a learning curve, but , yes, I have a basic insite and knowledge of the WP_Query, but would not even know, just yet, how to insert the first SELECT into a WP_Query. Searching around the internet for examples, because thats where I learn best, I havent found anything closely related to a Radius search without it being a custom SQl query and all suggestions relating to such a search, suggest using a Custom query. I just found the Geo Data Store plugin and will try to get the answer from that. – Malisa May 5 '15 at 12:38
  • Tom, thats just given me an idea.. maybe best to get the intial radius properties ID's and then do a seperate query on the taxonomy for the contract types.. Thanks. – Malisa May 5 '15 at 12:39
0

Well, they say perseverance is the key... Having found a link in the RELATED questions.. https://stackoverflow.com/questions/17588525/how-to-join-post-meta-and-taxonomy-table-wpdb-mysql, I got the answer. Ok, its not using Native parameters as suggested, nor does it give excessive load times as thought (its only displaying 5 properties max in a sidebar widget).

Not Everyone who asks a question here is looking for someone to do the work for them, more looking for guidance, constructive guidance and help.

Its NOT pretty and could be done better code, But for anyone else searching.. it may help.

 $dist='3.427'; /* Kilometers*/
 $orig_lat = get_post_meta ( $post_id, "_property_latitude",true);
 $orig_lon = get_post_meta ( $post_id, "_property_longitude",true);
 $lon1 = (float) $orig_lon - (int) $dist / abs( cos( deg2rad( (float) $orig_lon ) ) * 69 );
 $lon2 = (float) $orig_lon + (int) $dist / abs( cos( deg2rad( (float) $orig_lon ) ) * 69 );
 $lat1 = (float) $orig_lat - ( (int) $dist / 69 );
 $lat2 = (float) $orig_lat + ( (int) $dist / 69 );

 /* the 6371 is Radius of the Earth in kilometers - for miles multiply by 0.621371192 */

 $mapsearchquery = "SELECT DISTINCT `t`.`ID`,
6371 * 2 * ASIN( SQRT( POWER( SIN( ( '".$orig_lat."' - `t`.`latitude` ) * pi() / 180 / 2), 2 ) + COS( '".$orig_lat."' * pi() / 180) * COS( `t`.`latitude` * pi() / 180 ) * POWER( SIN( ( '".$orig_lon."' - `t`.`longitude` ) * pi() / 180 / 2 ), 2 ) ) ) AS `distance` 
 FROM (
SELECT `$wpdb->posts`.`ID`,
MAX(CASE WHEN `$wpdb->postmeta`.`meta_key` = '_property_longitude' THEN `$wpdb->postmeta`.`meta_value` END ) AS `longitude`,
MAX(CASE WHEN `$wpdb->postmeta`.`meta_key` = '_property_latitude' THEN `$wpdb->postmeta`.`meta_value` END ) AS `latitude`
FROM `$wpdb->posts` 
LEFT JOIN `$wpdb->postmeta` ON ( `$wpdb->posts`.`ID` = `$wpdb->postmeta`.`post_id` )
INNER JOIN `$wpdb->term_relationships` ON (`$wpdb->posts`.`ID` = `$wpdb->term_relationships`.`object_id`)
INNER JOIN `$wpdb->term_taxonomy` ON (`$wpdb->term_relationships`.`term_taxonomy_id` = `$wpdb->term_taxonomy`.`term_taxonomy_id`)
INNER JOIN `$wpdb->terms` ON (`$wpdb->terms`.`term_id` = `$wpdb->term_taxonomy`.`term_id`)
WHERE `$wpdb->posts`.`post_status` = 'publish'
AND `$wpdb->term_taxonomy`.`taxonomy` = 'contract_type' AND `$wpdb->terms`.`term_id` = ('".$_contract_type."')
AND `$wpdb->posts`.`post_type` = 'dt_properties' 
AND `$wpdb->postmeta`.`post_id` != '".$post_id."' 
GROUP BY `$wpdb->posts`.`ID` 
HAVING `longitude` BETWEEN '".$lon1."' AND '".$lon2."' AND `latitude` BETWEEN '".$lat1."' AND '".$lat2."') AS `t`
HAVING distance < '".$dist."'
ORDER BY distance ASC;";

// Just get the ID's
//$pageposts = $wpdb->get_col($mapsearchquery);
$pageposts = $wpdb->get_results($mapsearchquery, OBJECT);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.