按 sku 获取产品 min_price [英] Get product min_price by sku

问题描述

我需要通过 sku 获取产品 post_type 和草稿 post_status 的 min_price 最小值.

I need to get the smallest value of min_price by sku for product post_type and draft post_status.

function get_product_min_price_by_sku($sku) {
  global $wpdb;
  $min_price = $wpdb->get_var(
    $wpdb->prepare("
      SELECT MIN(min_price) 
      FROM {$wpdb->wc_product_meta_lookup} 
      WHERE sku = '$sku'
    ")
  );
  return $min_price;
}

如何在此功能中添加帖子状态条件?

How can I add the post status condition to this function?

AND posts.post_status = 'draft'

顺便说一句.我允许具有相同 sku 的多个产品:

BTW. I allow multiple products with same sku:

add_filter('wc_product_has_unique_sku', '__return_false', PHP_INT_MAX);

推荐答案

为了通过 post_status 过滤查询，我们首先需要在正确的表中查找值，即 wp 帖子表，我们可以通过使用 JOIN 语句更新我们的 SQL 查询并匹配多个表中的相关值来实现，在这种情况下，我们匹配 wp.postsproduct_id> wp.postmeta 表中的表.

In order to filter the query via the post_status we first need to look for the value in the correct table which is the wp posts table, we can do so by updating our SQL query using the JOIN statement and match the correlating values in multiple tables, in this case we are matching the product_id in the wp.posts table with the one in the wp.postmeta table.

我们使用 JOIN 语句匹配的表:

The tables we are matching using the JOIN statement:

• wp.posts $wpdb->posts
• wp.postmeta $wpdb->wc_product_meta_lookup

下面是一个更新的函数:

Below is an updated function:

function get_product_min_price_by_sku( $sku ) {
    global $wpdb;

    $min_price = $wpdb->get_var(
        $wpdb->prepare(
            "
            SELECT MIN(lookup.min_price) 
            FROM {$wpdb->wc_product_meta_lookup} AS lookup
            INNER JOIN {$wpdb->posts} AS posts ON lookup.product_id = posts.id
            WHERE
            posts.post_type IN ( 'product', 'product_variation' )
            AND posts.post_status = 'publish'
            AND lookup.sku = '%s'
            LIMIT 1
            "
        , $sku)
    );

    return $min_price;
}

这篇关于按 sku 获取产品 min_price的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！