Question 273140 in Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress – SQL join issues with postmeta

I am trying to build an app, get the location of the user and find the nearest shop to the current location. The data exists on a wordpress backend. I have successfully created the custom end point and ran the function. It all works as intended with one exception. When the data doesn’t exist it drops off that record from the returned results.

The information is stored within a custom post type (wp_shops) and the lat, long, address fields etc are all stored in the post meta. It returns correctly if all fields exist in the meta, but if one doesn’t (for example Shop 2 may not need to use the address2 field and as such not filled in. If not filled in it doesnt exist in the post meta table). I used this as a starting point: https://shorturl.at/gnSX3 and below is where it is at currently.

 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

Because "address 2" is missing from the table it doesn’t return any information for post_id 100037 (Shop 2).

Any help appreciated to get it to return the results even if one of the fields is not showing in the meta table.

I have seen similar issues but not managed to solve it yet. I have tried left join, right join, is null etc. But all with no luck. I am new to mySQL queries so possible I’m missing the obvious or not constructing it properly. Current code looks like this:

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";
}
Tags:

2

Answers


  1. 0

    Have you tried simply not querying for the address2 field?

    All you seem to need is an ID to identify the store and the latitude and longitude to calculate the geographical distance.

    Once you have an ordered list of ids you can fetch whatever else you might need.

    I imagine you’re trying to do everything in one efficient query for performance reasons? I don’t know the size of your database, but I can’t imagine it having much impact unless you have hundreds of stores to deal with.

    Login or Signup to reply.
  2. 0

    You have this pattern in your query to retrieve each item of postmeta.

             FROM $wpdb->posts p
    LEFT JOIN $wpdb->postmeta latitude ON p.ID = latitude.post_id
 ...
        WHERE ...
          AND latitude.meta_key = 'wpshops_lat'

    Change it to

             FROM $wpdb->posts p
    LEFT JOIN $wpdb->postmeta latitude ON p.ID = latitude.post_id
                                      AND latitude.meta_key = 'wpshops_lat'
 ...
        WHERE ...

    You need to mention the meta_key criterion in the ON clause for each LEFT JOIN, not in the query-wide WHERE clause. Anytime you mention a column in a LEFT JOINed table in the WHERE clause, it converts the LEFT JOIN to an ordinary inner JOIN. That suppresses rows from the result set missing the metadata.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search