Sql Query To Include Specific Woocommerce Tag

I want to modify a WooCommerce plugin in order to pick products of a selected tag of the name 'right-tag' for example. My Code is of the form: $query =$wpdb->prepare( 'SELECT *

Solution 1:

MySQL query to get product by specific tag:

SELECT posts.ID AS product_id,
       posts.post_title AS product_title
FROM wp_posts AS posts,
     wp_terms AS terms,
     wp_term_relationships AS term_relationships,
     wp_term_taxonomy AS term_taxonomy
WHERE term_relationships.object_id = posts.ID
  AND term_taxonomy.term_id = terms.term_id
  AND term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id
  AND posts.post_type = 'product'AND posts.post_status = 'publish'AND term_taxonomy.taxonomy = 'product_tag'AND terms.slug = 'my-tag-1'; -- Replace it with your product tag

---

Equivalent WordPress/PHP query

global$wpdb;
$query = "SELECT posts.ID AS product_id,
            posts.post_title AS product_title
     FROM {$wpdb->prefix}posts AS posts,
          {$wpdb->prefix}terms AS terms,
          {$wpdb->prefix}term_relationships AS term_relationships,
          {$wpdb->prefix}term_taxonomy AS term_taxonomy
     WHERE term_relationships.object_id = posts.ID
       AND term_taxonomy.term_id = terms.term_id
       AND term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id
       AND posts.post_type = 'product'
       AND posts.post_status = 'publish'
       AND term_taxonomy.taxonomy = 'product_tag'
       AND terms.slug = 'my-tag-1';"; //Replace it with your product tag$products = $wpdb->get_results($query);

if (!empty($products))
{
    //looping through all the productsforeach ($productsas$key => $product)
    {
        //...$product->product_id;
        $product->product_title;
        //...
    }
}

Both MySQL query & Code is tested and works.

Hope this helps!