php - 包含逗号的 orderby price meta_value - wordpress

Question

我有一些帖子应该按价格自定义字段(从高到低)排序。使用以下选项正确排序它们:

选项 1

$args = array(
    'posts_per_page' => -1,
    'post_type' => 'my-post-type', 
    'post_status' => 'publish',
    'meta_key' => 'price_field',
    'orderby' => 'meta_value_num',
    'order' => 'DESC',
);
$list = get_posts($args);

选项 2

global $wpdb;
$queryStr = "SELECT * FROM wp_posts AS table1 LEFT JOIN (SELECT * FROM wp_postmeta WHERE 
meta_key = 'price_field') AS table2 ON table1.ID = table2.post_id WHERE 
table1.post_type = 'my-post-type' AND table1.post_status = 'publish' order by 
table2.meta_value+0 DESC";
$list = $wpdb->get_results($queryStr);

选项 3(与选项 2 相同，只是具有 REPLACE 功能)

$queryStr = "SELECT * FROM wp_posts AS table1 LEFT JOIN (SELECT * FROM wp_postmeta WHERE 
meta_key = 'price_field') AS table2 ON table1.ID = table2.post_id WHERE 
table1.post_type = 'my-post-type' AND table1.post_status = 'publish' order by 
REPLACE(table2.meta_value, ',', '') DESC";

帖子的排序如下(顺序错误):

600
25,000
22,000
15,000
10,000
7,000
6,000
2,000
0

在我添加价格为 600 的帖子之前，排序一直很好。只要其中没有逗号，它就会出现在循环的最开始，这是不正确的。

我错过了什么吗？

Answer 1

您可以在 order by 子句中使用(伪造的)lpad 构建有效订单，例如:

$queryStr = "SELECT * 
             FROM wp_posts AS table1 
             LEFT JOIN (SELECT * 
             FROM wp_postmeta 
             WHERE meta_key = 'price_field') AS table2 ON table1.ID = table2.post_id 
             WHERE  table1.post_type = 'my-post-type' AND table1.post_status = 'publish' 
             order by  lpad(table2.meta_value, 12, '0') ASC";

引用:-

String Functions

或者根据您已经登录的事实，您可以尝试转换

$queryStr = "SELECT * 
             FROM wp_posts AS table1 
             LEFT JOIN (SELECT * 
                  FROM wp_postmeta 
                  WHERE meta_key = 'price_field') AS table2 ON table1.ID = table2.post_id 
             WHERE  table1.post_type = 'my-post-type' AND table1.post_status = 'publish' 
             order by  CONVERT(REPLACE(table2.meta_value, ',', ''),SIGNED INTEGER) ASC";