WooCommerce sql query to find products with a specific meta key and meta value

I am trying to get products with a custom field with value condition from DB.

Custom field is named: “_filtered_product”.
I want to get products only with value = 1

This is SQL query I have written.

$view_low_filt_sql =
 "LEFT JOIN $wpdb->postmeta manage ON (p.ID = manage.post_id AND manage.meta_key = '_manage_stock')
  LEFT JOIN $wpdb->postmeta stock ON (p.ID = stock.post_id AND stock.meta_key = '_stock')
  LEFT JOIN $wpdb->postmeta threshold ON (p.ID = threshold.post_id AND threshold.meta_key = '_low_inventory_number')
  LEFT JOIN $wpdb->postmeta filtered ON (p.ID = filtered.post_id AND filtered.meta_key = '_filtered_product')
  WHERE (p.post_type = 'product' OR p.post_type = 'product_variation')
  AND (p.post_status = 'publish' OR p.post_status = 'private' OR p.post_status = 'draft')
  AND manage.meta_value = 'yes'
  AND threshold.meta_value IS NOT NULL
  AND filtered.meta_value = '1'
  AND IFNULL(CAST(stock.meta_value AS SIGNED),0) <= CAST(threshold.meta_value AS SIGNED)
  ";

Answer

Not sure why you want to write your own sql query for this simple task, while you could use wp_query!

That being said, you could use the following snippet to get all products with a specific meta key and meta value:

global $wpdb;

$meta_key = '_filtered_product';

$meta_value = '1';

$sql_statement = "SELECT {$wpdb->prefix}posts.* FROM {$wpdb->prefix}posts INNER JOIN {$wpdb->prefix}postmeta ON ( {$wpdb->prefix}posts.ID = {$wpdb->prefix}postmeta.post_id ) WHERE 1=1 AND ( ( {$wpdb->prefix}postmeta.meta_key = '{$meta_key}' AND {$wpdb->prefix}postmeta.meta_value = '{$meta_value}' ) ) AND {$wpdb->prefix}posts.post_type = 'product' AND ({$wpdb->prefix}posts.post_status = 'publish' OR {$wpdb->prefix}posts.post_status = 'private') GROUP BY {$wpdb->prefix}posts.ID ORDER BY {$wpdb->prefix}posts.post_date DESC";

$sql = $wpdb->prepare($sql_statement);

$sql_results = $wpdb->get_results($sql, ARRAY_A);

if ($sql_results) {

  foreach ($sql_results as $result) {

    echo "<p>Product id: " . $result['ID'] . " and product title: " . $result['post_title'] . "<p>";

  }

} else {

  echo "Sorry can't find anything at the moment!";

}

wp_reset_postdata();

Which will output this:

Product id: {the product id} and product title: {the product name that matches the query} 

Note:

• The query above, will retrieve all of the data for a product. I just used ID and title to give you an example.
• If your $meta_value is string, then you’re all set, meaning I’ve written the query, assuming that you meta value is string. However, if it’s NOT, then use this {$meta_value} instead of '{$meta_value}'.
• I’ve used global $wpdb and took advantage of its properties and methods.
• Instead of hard-coding your database tables prefix which by default would be wp_, I’ve used {$wpdb->prefix}.
• I’ve also used $wpdb->prepare statement to secure the sql statement.
• Also, I’ve used ARRAY_A as the second argument in the $wpdb->get_results function, to get the data as an associative array!
• Also, be aware that this query is ordered by date, descending, meaning recent products will get outputted first!

---

Performing the same query using wp_query:

$meta_key = '_filtered_product';

$meta_value = '1';

$args = array(
  'post_type'      => 'product',
  'posts_per_page' => -1,
  'meta_query'     => array(
    array(
      'key'     => $meta_key,
      'value'   => $meta_value,
      'compare' => '=',
    )
  )
);

$woo_custom_query = new WP_Query($args);

if ($woo_custom_query->have_posts()) {

  while ($woo_custom_query->have_posts()) {

    $woo_custom_query->the_post();

    the_title();
  }

} else {

  echo "Sorry can't find anything at the moment!";

}

wp_reset_postdata();

This answer has been fully tested on woocommerce 5.x+ and works fine!