postmeta 的 SQL 联接问题

问：

我正在尝试构建一个应用程序，获取用户的位置并找到离当前位置最近的商店。数据存在于 wordpress 后端。我已经成功创建了自定义端点并运行了该函数。除了一个例外，一切都按预期工作。当数据不存在时，它会从返回的结果中删除该记录。

信息存储在自定义帖子类型 （wp_shops） 中，纬度、经度、地址字段等都存储在帖子元中。如果 meta 中存在所有字段，则返回正确，但如果不存在（例如，商店 2 可能不需要使用 address2 字段，因此不填写。如果未填写，则在帖子元表中不存在它）。我以此为起点：https://shorturl.at/gnSX3 及以下是它目前所处的位置。

 Post Meta Example data:

meta_id  |  post_id   |  meta_key         |  meta_value
78652    |  100036    |  wpshops_address  |  Somewhere
78653    |  100036    |  wpshops_address2 |  Someplace
78654    |  100036    |  wpshops_city     |  Somecity
78655    |  100036    |  wpshops_state    |  Somestate
78656    |  100036    |  wpshops_zip      |  AZIP-CODE
78657    |  100036    |  wpshops_country  |  Some Country
78658    |  100036    |  wpshops_phone    |  01234 567 8901
78652    |  100037    |  wpshops_address  |  Somewhere2
78654    |  100037    |  wpshops_city     |  Somecity2
78655    |  100037    |  wpshops_state    |  Somestate2
78656    |  100037    |  wpshops_zip      |  AZIP-CODE2
78657    |  100037    |  wpshops_country  |  Some Country2
78658    |  100037    |  wpshops_phone    |  01234 567 8902

Post Table
ID      |  post_title  |  post_type
100036  |  Shop 1      |  wp_shops
100037  |  Shop 2      |  wp_shops

由于表中缺少“地址 2”，因此它不会返回post_id 100037（商店 2）的任何信息。

感谢任何帮助，即使其中一个字段未显示在元表中，也可以返回结果。

我见过类似的问题，但还没有设法解决它。我尝试过左连接、右连接、为 null 等。但这一切都没有运气。我是mySQL查询的新手，所以我可能错过了显而易见的东西或没有正确构建它。当前代码如下所示：

function my_nearest_shop() {

    global $wpdb;
    $distance = "50 miles";
    $lat =  "37.831313606716186";
    $lng = "-122.038259135056737";
 
    $earth_radius = 3959;

    $sql = $wpdb->prepare( "
        SELECT DISTINCT
            p.ID,
            p.post_title,
            latitude.meta_value as locLat,
            longitude.meta_value as locLong,
            address.meta_value as address,
            address2.meta_value as address2,
            city.meta_value as city,
            state.meta_value as state,
            zip.meta_value as zip,
            country.meta_value as country,
            phone.meta_value as phone,
            ( %d * acos(
            cos( radians( %s ) )
            * cos( radians( latitude.meta_value ) )
            * cos( radians( longitude.meta_value ) - radians( %s ) )
            + sin( radians( %s ) )
            * sin( radians( latitude.meta_value ) )
            ) )
            AS distance
        FROM $wpdb->posts p
    LEFT JOIN $wpdb->postmeta latitude ON p.ID = latitude.post_id
    LEFT JOIN $wpdb->postmeta longitude ON p.ID = longitude.post_id
        LEFT JOIN $wpdb->postmeta address ON p.ID = address.post_id
        LEFT JOIN $wpdb->postmeta address2 ON p.ID = address2.post_id
        LEFT JOIN $wpdb->postmeta city ON p.ID = city.post_id
        LEFT JOIN $wpdb->postmeta state ON p.ID = state.post_id
    LEFT JOIN $wpdb->postmeta zip ON p.ID = zip.post_id
        LEFT JOIN $wpdb->postmeta country ON p.ID = country.post_id
        LEFT JOIN $wpdb->postmeta phone ON p.ID = phone.post_id
        WHERE 1 = 1
        AND p.post_type = 'wp_shops'
        AND p.post_status = 'publish'
        AND latitude.meta_key = 'wpshops_lat'
        AND longitude.meta_key = 'wpshops_lng'
        AND address.meta_key = 'wpshops_address'
        AND address2.meta_key = 'wpshops_address2'
        AND city.meta_key = 'wpshops_city'
        AND state.meta_key = 'wpshops_state'
        AND zip.meta_key = 'wpshops_zip'
        AND country.meta_key = 'wpshops_country'
        AND phone.meta_key = 'wpshops_phone'
        HAVING distance < %s
        ORDER BY distance ASC",
        $earth_radius,
        $lat,
        $lng,
        $lat,
        $distance
);
 
$nearbyLocations = $wpdb->get_results( $sql );
 
if ( $nearbyLocations ) {
return $nearbyLocations;
}
else{
    return "no results";
}