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)
  ";

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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!

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0