How to get a WooCommerce product by SKU when you are using WPML?

WooCommerce and WPML are two very popular plugins for WordPress that are very often used together, which is not surprising since online stores are often multinational and need to appeal to a wide variety of users from across the globe, preferably in their native language. The integration of these plugins usually runs without any problems, however we may encounter difficulties when we need to add our own code that performs additional operations – for example, creating a WooCommerce product based on its SKU.

A typical code function which returns a product from the WooCommerce plugin based on its SKU may look like this:

function get_product_by_sku( $sku ) {
    global $wpdb;
    $product_id = $wpdb->get_var($wpdb->prepare("SELECT post_id FROM $wpdb->postmeta WHERE meta_key='_sku' AND meta_value='%s' LIMIT 1", $sku));
    if ($product_id) {
        return new WC_Product($product_id);
    }
    return null;
}

Unfortunately, this ignores the fact that WPML creates a separate record in the table entries for each product, so the product we receive is in a random language – depending on which record returns as the first in a given SKU database.

Fortunately, we can fix this problem relatively simply, as we only need to modify the query that retrieves the ID of the product:

function get_product_by_sku($sku) {
    global $wpdb;

    $product_id = $wpdb->get_var($wpdb->prepare("SELECT pm.post_id FROM ".$wpdb->postmeta." AS pm LEFT JOIN ".$wpdb->prefix."icl_translations AS tr ON pm.post_id = tr.element_id WHERE pm.meta_key='_sku' AND pm.meta_value='%s' AND tr.language_code = '".ICL_LANGUAGE_CODE."'", $sku));

    if($product_id) {
        return new WC_Product($product_id);
    }
    return null;
}

The above code works similarly to the previous snippet of code, but during the query data is retrieved from the icl_translations table – with this we are able to specify the language of the table entry. This table stores the relationships between languages ​​and entries for the WPML plugin – it is an easy way to obtain information about which entry is associated with a given language. Of course, the most important is the last condition, which selects only the record that is associated with the currently used language on the site – this gives us a record of the Product ID in the given language.

Then, we are just left with creating an object of class WC_Product based on the downloaded ID. We have to remember not to use the cache for this type of function, because we may receive the product data in the wrong language. Alternatively, you could use a cache which will immediately recognize the current language, and use this to decide whether the results of operation have been added to the cache.