Efficiently Customizing WooCommerce Prices and Product Visibility by Customer

In a recent Business Bloomer Club Slack thread, a WooCommerce developer sought a solution to display unique product prices and product visibility for each of 400+ customers.

Standard B2B plugins didn’t meet the requirements, so the idea was to use custom fields to assign prices and visibility rules per customer.

Here’s an alternative, scalable approach to manage personalized pricing and visibility.

Challenge 1: Custom Pricing per Customer

With each of the 400+ customers requiring a unique price per product, using custom fields or postmeta might create performance issues. Instead, using a custom database table could significantly improve performance and scalability.

Step-by-Step Solution

1. Create a Custom Database Table:

• Structure the table with rows for each customer and columns for each product:

1. Retrieve Prices for Logged-In Customers:

• Use WooCommerce hooks to load the custom price when a logged-in customer views a product. For example:

function get_custom_price_for_customer( $product_id, $customer_id ) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'custom_prices';
    $price = $wpdb->get_var( $wpdb->prepare(
        "SELECT `p:%d` FROM $table_name WHERE `CID` = %d",
        $product_id, $customer_id
    ) );
    return $price;
}

1. Cache and Transients:

• Consider caching the results with transients to reduce database load. This will improve performance, especially with large datasets.

Challenge 2: Restricting Product Visibility by Customer

The goal here is to ensure each customer only sees certain products. Using a similar database approach with serialized data can allow efficient control over product visibility.

Solution

1. Add Visibility Column in Database Table:

• Add a visible column that stores product IDs in serialized format:

1. Retrieve and Control Visibility:

• Use WooCommerce’s woocommerce_product_is_visible filter to check the visibility rules from your database:

add_filter( 'woocommerce_product_is_visible', 'control_product_visibility_for_customer', 10, 2 );

function control_product_visibility_for_customer( $visible, $product_id ) {
    $customer_id = get_current_user_id();
    $allowed_products = get_allowed_products_for_customer( $customer_id );

    if ( ! in_array( $product_id, $allowed_products ) ) {
        $visible = false;
    }

    return $visible;
}

1. Helper Function to Fetch Allowed Products:

• Use a helper function to retrieve the serialized list of visible product IDs for each customer:

function get_allowed_products_for_customer( $customer_id ) {
    global $wpdb;
    $table_name = $wpdb->prefix . 'custom_prices';
    $serialized_data = $wpdb->get_var( $wpdb->prepare(
        "SELECT `visible` FROM $table_name WHERE `CID` = %d",
        $customer_id
    ) );
    return unserialize( $serialized_data );
}

Conclusion

Using a custom database table for managing unique customer prices and visibility rules is an efficient, scalable solution compared to using postmeta fields. This approach keeps performance stable, especially with a high volume of data, and offers flexibility for future growth. By caching results and organizing data optimally, you can streamline personalized pricing and visibility while ensuring a smooth customer experience.