在后元价值上生成RADIUS搜索并添加要查询的分类

这是一条早在2011年就出现在这里的旧SQL语句，但它是我可以找到并处理所需内容的壁橱。虽然它完全符合我的需要，在当前显示的属性的给定半径内显示属性，但我需要在中添加合同类型的分类，以便它只显示属性、给定半径和合同类型。

我尝试了各种方法，但不太清楚如何加入术语/术语关系表来实现这一点。

任何帮助都将不胜感激。

这是我目前掌握的代码（我知道很混乱）。

  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;

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

相关推荐