Generating a radius search on postmeta-metavalue and adding a taxonomy to query

Question

Generating a radius search on postmeta-metavalue and adding a taxonomy to query

Malisa

2015年5月5日 16:02

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;

Topic location-search custom-taxonomy custom-post-types Wordpress

Category Web

Malisa · Accepted Answer · 2015年5月5日 16:02

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);

Generating a radius search on postmeta-metavalue and adding a taxonomy to query

About