Table remain empty

Please add the following lines into the top of the acf_google_maps_search_save_post function

// now update historic post data
$acf_gms = new acf_gms;
$acf_gms->update_historic_post_gm_data();
exit;

Then save any post.
This should update historic products. Finally, remove these lines

nevertheless, I don’t think that the order of displayed posts is made according to the nearest coordinates…

my url returned by my search form is https://localhost/?s=&post_type=product&lat=43.215134&lng=5.537119999999959

and if I echo the $join and $orderby query it returns
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 (POW((acf_gms_geo.lng-5.537119999999959),2) + POW((acf_gms_geo.lat-43.215134),2)) ASC

Do you think it’s correct?

• This reply was modified 7 years, 1 month ago by jeff2mars.

Doesn’t look right
try chaning line 98 of functions.php to

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

Oh hang on, I thought you meant you were echoing the whole query.

Try echoing the whole search query and debug from there.

The above individual echo’s are correct

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

• This reply was modified 7 years, 1 month ago by jeff2mars.

no idea ?

I identified the problem. I have two meta_query in my functions.php to display products in two different places of my page according to two different rules.

And the ORDER BY location triggered by the plugin only appears in one of the sql query but not in this one (although the LEFT JOIN part appears) :

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 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_posts.ID NOT IN (968) AND (
wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (10)
)
) AND (
wp_postmeta.meta_key = ‘total_sales’
AND
(
(
( mt1.meta_key = ‘flash_sale_start’ AND CAST(mt1.meta_value AS DATE) <= ‘20180125’ )
AND
( mt2.meta_key = ‘flash_sale_end’ AND CAST(mt2.meta_value AS DATE) >= ‘20180125’ )
)
)
) AND wp_posts.post_type = ‘product’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘complete’ OR wp_posts.post_status = ‘paid’ OR wp_posts.post_status = ‘confirmed’ OR wp_posts.post_status = ‘unpaid’ OR wp_posts.post_status = ‘pending-confirmation’ OR wp_posts.post_status = ‘cancelled’ OR wp_posts.post_status = ‘private’)
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 DESC, wp_posts.post_date DESC LIMIT 0, 100

• This reply was modified 7 years, 1 month ago by jeff2mars.
• This reply was modified 7 years, 1 month ago by jeff2mars.

I have this meta_query :

$today = current_time('Ymd');
 $args = array (
    'numberposts' => -1,
    'post_status'       => 'publish',
    'post_type'         => 'product',
    'meta_query' => array(
    'relation' => 'AND',
    'start_clause' => array(
        'key'=>'flash_sale_start',
        'value' => $today,
        'compare'=> '<=',
     'type' => 'DATE'
    ),
         'end_clause' => array(
         'key' => 'flash_sale_end',
     'value' => $today,
         'compare' => '>=',
     'type' => 'DATE'
)

       )
);
return $args;
}

// The main shop and archives meta query
add_filter( 'woocommerce_product_query_meta_query', 'custom_product_query_meta_query', 10, 2 );
function custom_product_query_meta_query( $meta_query, $query ) {
    if( ! is_admin() ) 
        return custom_meta_query( $meta_query );
}

from which depends this sql query that doesn’t display the orderby location :

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 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_posts.ID NOT IN (968) AND (
wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (10)
)
) AND (
wp_postmeta.meta_key = ‘total_sales’
AND
(
(
( mt1.meta_key = ‘flash_sale_start’ AND CAST(mt1.meta_value AS DATE) <= ‘20180125’ )
AND
( mt2.meta_key = ‘flash_sale_end’ AND CAST(mt2.meta_value AS DATE) >= ‘20180125’ )
)
)
) AND wp_posts.post_type = ‘product’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘complete’ OR wp_posts.post_status = ‘paid’ OR wp_posts.post_status = ‘confirmed’ OR wp_posts.post_status = ‘unpaid’ OR wp_posts.post_status = ‘pending-confirmation’ OR wp_posts.post_status = ‘cancelled’ OR wp_posts.post_status = ‘private’)
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 DESC, wp_posts.post_date DESC LIMIT 0, 100

And I have also this meta query :

$products = new WP_Query( array (
            'post_type'         => 'product',
            'post_status'       => 'publish',
            'posts_per_page'    => $atts['limit'],
            'meta_query'        => array(
                'relation'      => 'AND',
                'start_clause'  => array(
                    'key'       =>'flash_sale_start',
                    'value'     => $today,
                    'compare'   => '>',
                    'type'      => 'DATE'
                ),
                'end_clause'    => array(
                    'key'       => 'flash_sale_end',
                    'value'     => $today,
                    'compare'   => '>',
                    'type'      => 'DATE'
                ),
		'limit_clause'    => array(
                    'key'       => 'flash_sale_start',
                    'value'     => $limitdate,
                    'compare'   => '<=',
                    'type'      => 'DATE'
                )
)

        ));
}

        ob_start();

        if ( $products->have_posts() ) { ?>

            <?php woocommerce_product_loop_start(); ?>

                <?php while ( $products->have_posts() ) : $products->the_post(); ?>

                    <?php wc_get_template_part( 'content', 'product' ); ?>

                <?php endwhile; // end of the loop. ?>

            <?php woocommerce_product_loop_end(); ?>

            <?php
        } else {
            do_action( "woocommerce_shortcode_products_loop_no_results", $atts );
            echo "<p>Aucune vente à venir pour le moment.</p>";
        }

        woocommerce_reset_loop();
        wp_reset_postdata();

        return '<div class="woocommerce columns-' . $atts['columns'] . '">' . ob_get_clean() . '</div>';
    }

    add_shortcode( 'upcoming_sales', 'upcoming_sales' );

from which depends this sql_query displaying the orderby location :

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 know why the plugin cannot do the orderby in the first sql query ?

I managed to make it work!!!

As woocommerce has its own query to order products, it works by making a function such as

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 ) {
  
		$args['orderby'] = ' (POW((acf_gms_geo.lng-{$lng}),2) + POW((acf_gms_geo.lat-

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