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)
";
Santiago Trujillo
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:
ID and title to give you an example.$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}'.$wpdb and took advantage of its properties and methods.wp_, I've used {$wpdb->prefix}.$wpdb->prepare statement to secure the sql statement.ARRAY_A as the second argument in the $wpdb->get_results function, to get the data as an associative array!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!