douzen1880 2017-05-03 22:50
浏览 63
已采纳

在类别页面中显示最低的简单产品价格

I want to display the lowest simple product price in the content-oroduct_cat.php page. The following code from Fancy Squares works for showing the lowest price but I want to show Simple products only, i.e. omit grouped products.

/* SHOW LOWEST PRICE ON CATEGORY PAGE */
//woocommerce get lowest price in category
function wpq_get_min_price_per_product_cat($term_id)
{    
    global $wpdb;

    $sql = "
    SELECT  MIN( meta_value+0 ) as minprice
    FROM {$wpdb->posts} 
    INNER JOIN {$wpdb->term_relationships} ON ({$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id)
    INNER JOIN {$wpdb->postmeta} ON ({$wpdb->posts}.ID = {$wpdb->postmeta}.post_id) 
    WHERE  
      ( {$wpdb->term_relationships}.term_taxonomy_id IN (%d) ) 
    AND {$wpdb->posts}.post_type = 'product'  
    AND {$wpdb->posts}.post_status = 'publish' 
    AND {$wpdb->postmeta}.meta_key = '_price'
    ";

    return $wpdb->get_var($wpdb->prepare($sql, $term_id));
}

I tried using :

AND {$wpdb->posts}.product_type = 'simple'

but this didn't work. How would I display only simple products?

  • 写回答

1条回答 默认 最新

  • dongxichan8627 2017-05-03 23:36
    关注

    You query is not working because product_type is not stored in posts table it is stored in term_taxonomy table. To get the desired you have to use Sub query, which will fetch all the simple product and the main query filter it according to category.

    I have modified your wpq_get_min_price_per_product_cat() as below

    function wh_get_min_price_per_product_cat($term_id)
    {
        global $wpdb;
    
        $sql = "
        SELECT  MIN( meta_value+0 ) as minprice
        FROM {$wpdb->posts} 
        INNER JOIN {$wpdb->term_relationships} ON ({$wpdb->posts}.ID = {$wpdb->term_relationships}.object_id)
        INNER JOIN {$wpdb->postmeta} ON ({$wpdb->posts}.ID = {$wpdb->postmeta}.post_id) 
        WHERE  
          ( {$wpdb->term_relationships}.term_taxonomy_id IN (%d) ) 
            AND {$wpdb->posts}.post_type = 'product' 
            AND {$wpdb->posts}.post_status = 'publish' 
            AND {$wpdb->postmeta}.meta_key = '_price'
            AND {$wpdb->posts}.ID IN (SELECT posts.ID
                    FROM {$wpdb->posts} AS posts
                    INNER JOIN {$wpdb->term_relationships} AS term_relationships ON posts.ID = term_relationships.object_id
                    INNER JOIN {$wpdb->term_taxonomy} AS term_taxonomy ON term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id
                    INNER JOIN {$wpdb->terms} AS terms ON term_taxonomy.term_id = terms.term_id
                    WHERE term_taxonomy.taxonomy = 'product_type'
                    AND terms.slug = 'simple'
                    AND posts.post_type = 'product')";
        return $wpdb->get_var($wpdb->prepare($sql, $term_id));
    }
    

    Code goes in functions.php file of your active child theme (or theme). Or also in any plugin php files.

    USAGE

    echo wh_get_min_price_per_product_cat($cat_id);
    

    Code is tested and works.

    Reference: SQL query to check product_type in WooCommerce

    Hope this helps!

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 PADS Logic 原理图
  • ¥15 PADS Logic 图标
  • ¥15 电脑和power bi环境都是英文如何将日期层次结构转换成英文
  • ¥20 气象站点数据求取中~
  • ¥15 如何获取APP内弹出的网址链接
  • ¥15 wifi 图标不见了 不知道怎么办 上不了网 变成小地球了
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部