CODEWITHSUNDEEP
phpwordpresssortingsql-order-by
aiddev
aiddev

Reputation: 1429

orderby price meta_value which contains comma - wordpress

I have posts which should be sorted by price custom field (from high to low). Used below options to order them correctly:

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

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

OPTION 3 (same as option 2 just with REPLACE function)

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

Posts are being ordered as follows (in wrong order):

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

Sorting was good until I added post with 600 price. As long as it doesn't have comma in it, it appeared at the very start of the loop which is not correct.

Is there something that I missed?

Upvotes: 3

Views: 458

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

you could build a valid order using a (fake) lpad in the order by clause eg: 

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

Reference:-

String Functions

Or based on fact you have signbe you could try convert 

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

Upvotes: 2

Related Questions