代码之家 › 专栏 › 技术社区 › Jeff

按最近的地点订购产品

advanced-custom-fields woocommerce wordpress sql php

Jeff · 技术社区 · 8 年前

在搜索表单中输入位置后,我想按最近的位置订购产品。我使用ACF的谷歌地图为产品分配位置,该字段称为“地址”。我还为我的表单提供了Google places autocomplete和ACF Google maps radius搜索插件。我的表单重定向到包含lat和lng变量的URL:

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

插件应该从这个url订购产品。lat和lng在URL中传递良好。不幸的是,它并没有改变搜索产品的顺序。

以下是该插件的一些关键功能:

// 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');

下面是传递到结果页的sql查询:

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

你知道这个问题吗?

非常感谢。

3 回复 | 直到 8 年前

Jeff 8 年前

我发现了问题。我的函数中有两个meta\u查询。php根据两种不同的规则在我的页面中的两个不同位置显示产品。

插件触发的ORDER BY location只出现在一个sql查询中,而不出现在这个sql查询中(尽管左侧的连接部分出现):

    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 )

//下面您可以看到插件的le left join部分出现

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

你知道如何纠正这个问题吗?

Jeff 8 年前

我有这个meta\u查询:

$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 );
}

其中取决于以下不显示orderby位置的sql查询:

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) &lt;= â20180125â )
    AND
    ( mt2.meta_key = âflash_sale_endâ AND CAST(mt2.meta_value AS DATE) &gt;= â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

我还有一个元查询:

$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 &#224; 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' );

此sql\U查询显示插件触发的orderby位置,该查询依赖于此sql\U查询:

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) &gt; â20180122â 
            )
            AND ( mt1.meta_key = âflash_sale_endâ 
                AND CAST(mt1.meta_value AS DATE) &gt; â20180122â 
            )
            AND ( mt2.meta_key = âflash_sale_startâ 
                AND CAST(mt2.meta_value AS DATE) &lt;= â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

您知道为什么插件不能在第一个sql查询中执行orderby吗?

Jeff 8 年前

我成功了!!由于woocommerce有自己的产品订购功能,因此只需编写如下函数:

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;
    }