从SQL获取其他列

时间:2016-10-24 作者:Rafal

我有下面的功能来获取附近的帖子,我想实现的是从“rate\\u review”表中获取额外的自定义元。我如何实现它?

function get_nearby_locations2( $lat, $lng, $distance ) {
       global $wpdb;

    $earth_radius = 6371;

    $sql = $wpdb->prepare( "
        SELECT DISTINCT
            map_lat.post_id,
            p.post_title,
            p.ID,
            map_lat.meta_value as locLat,
            map_lng.meta_value as locLong,

             (
    6371 * ACOS(
      COS(RADIANS( %s )) * COS(RADIANS(map_lat.meta_value)) * COS(
        RADIANS(map_lng.meta_value) - RADIANS( %s )
      ) + SIN(RADIANS( %s )) * SIN(RADIANS(map_lat.meta_value))
    )
  ) AS distance



        FROM $wpdb->posts p
        INNER JOIN $wpdb->postmeta map_lat ON p.ID = map_lat.post_id
        INNER JOIN $wpdb->postmeta map_lng ON p.ID = map_lng.post_id
        WHERE 1 = 1
        AND p.post_type = \'beaches\'
        AND p.post_status = \'publish\'
        AND map_lat.meta_key = \'map_lat\'
        AND map_lng.meta_key = \'map_lng\'
        HAVING distance < \'10\'
        ORDER BY distance ASC",
        $lat,
        $lng,
        $lat
    );

    $nearbyLocations = $wpdb->get_results( $sql );

    if ( $nearbyLocations ) {
        return $nearbyLocations;
    }
}
返回列:

post\\u id
post\\u title
id
locLat
locLong
距离

1 个回复
最合适的回答,由SO网友:TheDeadMedic 整理而成

您需要添加另一个联接:

 INNER JOIN $wpdb->postmeta rate_review ON p.ID = rate_review.post_id
将条件添加到where子句中:

 AND rate_review.meta_key = \'rate_review\'
现在您可以将其添加到SELECT:

 rate_review.meta_value AS rate_review