WooCommerce如何编写SQL查询筛选_filtered_product值为1的产品

需要从数据库中查询符合自定义字段取值条件的产品，具体要求如下：

• 自定义字段名称为 "_filtered_product"
• 仅筛选该字段取值为1的产品

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

1. 逻辑正确性校验：当前写法已经满足筛选_filtered_product值为1的产品的要求，不过你对filtered表用了LEFT JOIN但后续WHERE子句限定了filtered.meta_value的取值，实际上会自动转化为INNER JOIN逻辑，如果你确定不需要保留没有该自定义字段的产品，可以直接把LEFT JOIN改成INNER JOIN，查询性能会更好。
2. 语法简化：可以把WHERE子句里的OR判断改为IN写法，可读性更强：

   WHERE p.post_type IN ('product', 'product_variation')
   AND p.post_status IN ('publish', 'private', 'draft')
   

3. 类型匹配注意：meta_value默认是字符串类型，你写的filtered.meta_value = '1'是正确的，不要去掉单引号，避免隐式类型转换导致索引失效。

内容的提问来源于stack exchange，提问作者Vladimir Kyatipov