Order products by nearest locations

Question

I want to order products by nearest locations after a location is entered in a search form. I use the Google Maps from ACF to assign location to products and the field is called "address". I have also Google places autocomplete for my form and the ACF google maps radius search plugin. My form redirect to an URL with the lat and lng variables:

/?s=&post_type=product&lat=43.26768079999999&lng=6.640710899999931

and the plugin is supposed to order products from this url. The lat and lng are well passed in the URL. Unfortunately it doesn't change anything to the order of the searched products.

Here are some key functions of the plugin:

// Join for searching metadata
function acf_google_maps_search_join_to_WPQuery($join) {

    global $wpdb;

    $acf_gms = new acf_gms; 
    $table_name = $acf_gms->table_name();

    if ( 
        isset($_GET['lat']) && !empty($_GET['lat']) 
        && isset( $_GET['lng']) && !empty($_GET['lng']) 
         ) {

        $join .= " LEFT JOIN {$table_name} AS acf_gms_geo ON {$wpdb->posts}.ID = acf_gms_geo.post_id ";

    }

    return $join;

}
add_filter('posts_join', 'acf_google_maps_search_join_to_WPQuery');



// ORDER BY DISTANCE
function acf_google_maps_search_orderby_WPQuery($orderby) {


     if ( 
        isset($_GET['lat']) && !empty($_GET['lat']) 
        && isset( $_GET['lng']) && !empty($_GET['lng']) 
         ) {

        $lat = sanitize_text_field( $_GET['lat'] );
        $lng = sanitize_text_field( $_GET['lng'] );

        $orderby = " (POW((acf_gms_geo.lng-{$lng}),2) + POW((acf_gms_geo.lat-{$lat}),2)) ASC";

    }

    return $orderby;

}
add_filter('posts_orderby', 'acf_google_maps_search_orderby_WPQuery');

and here is the sql query passed to the result page:

SELECT wp_posts.* 
FROM wp_posts 
INNER JOIN wp_postmeta 
    ON ( wp_posts.ID = wp_postmeta.post_id ) 
INNER JOIN wp_postmeta AS mt1 
    ON ( wp_posts.ID = mt1.post_id ) 
INNER JOIN wp_postmeta AS mt2 
    ON ( wp_posts.ID = mt2.post_id ) 
LEFT JOIN wp_acf_google_map_search_geodata AS acf_gms_geo 
    ON wp_posts.ID = acf_gms_geo.post_id 
WHERE 1=1 
    AND (   
            ( wp_postmeta.meta_key = ‘flash_sale_start’ 
                AND CAST(wp_postmeta.meta_value AS DATE) > ‘20180122’ 
            )
            AND ( mt1.meta_key = ‘flash_sale_end’ 
                AND CAST(mt1.meta_value AS DATE) > ‘20180122’ 
            )
            AND ( mt2.meta_key = ‘flash_sale_start’ 
                AND CAST(mt2.meta_value AS DATE) <= ‘20180127’ 
            )
        ) 
    AND wp_posts.post_type = ‘product’ 
    AND ((wp_posts.post_status = ‘publish’)) 
GROUP BY wp_posts.ID 
ORDER BY (POW((acf_gms_geo.lng-6.640710899999931),2) + POW((acf_gms_geo.lat-43.26768079999999),2)) ASC

Do you have an idea of the problem?

Thank you

Answer 1

I managed to make it work !! As woocommerce has its own thing to order products, it was just necessary to write a function like this :

if(isset($_GET['lat']) and $_GET['lat'] != "" and isset($_GET['lng']) and $_GET['lng'] != "")
    {
    add_filter( 'woocommerce_default_catalog_orderby', 'custom_woocommerce_get_catalog_ordering_args' 

    );
    }
    function custom_woocommerce_get_catalog_ordering_args( $args ) {

    $lat = sanitize_text_field( $_GET['lat'] );
    $lng = sanitize_text_field( $_GET['lng'] );

            $args['orderby'] = ' (POW((acf_gms_geo.lng-{$lng}),2) + POW((acf_gms_geo.lat-

    {$lat}),2))';
            $args['order'] = 'ASC';
            $args['meta_key'] = '';

        return $args;
    }