CODEWITHSUNDEEP
phpsqlwordpress
Youstart
Youstart

Reputation: 51

Order by meta_value in SQL request

I want to order my result by the number of likes. I need to use a sql request instead of a new query args.

Inside a regular query, I should use 'orderby' => 'meta_value_num' but because it is a sql request I use ORDER BY {$wpdb->prefix}postmeta.meta_value DESC;

This is what I tried:

$test = $wpdb->get_results ( "
SELECT * 
FROM  {$wpdb->prefix}postmeta
WHERE post_id LIKE '$get_the_ID_090909%' AND {$wpdb->prefix}postmeta.meta_key = '_liked' AND {$wpdb->prefix}postmeta.meta_value > 0
ORDER BY {$wpdb->prefix}postmeta.meta_value DESC;
        " );
        echo ' '.$test[0]->meta_value; // 2
        echo ' '.$test[1]->meta_value; // 14
        echo ' '.$test[2]->meta_value; // 10

The right order should be :

14
10
2

Please note that if I use "ASC", the order is 10, 14, 2 And if I change the like number, it DOES change the position. Sometimes it is the right order depending of the number of likes

var_dump() :

 array(3) { [0]=> object(stdClass)#7998 (4) { ["meta_id"]=> string(3) "756" ["post_id"]=> string(12) "179090909185" ["meta_key"]=> string(6) "_liked" ["meta_value"]=> string(1) "2" } [1]=> object(stdClass)#8000 (4) { ["meta_id"]=> string(3) "696" ["post_id"]=> string(12) "179090909176" ["meta_key"]=> string(6) "_liked" ["meta_value"]=> string(2) "14" } [2]=> object(stdClass)#8001 (4) { ["meta_id"]=> string(3) "697" ["post_id"]=> string(12) "179090909170" ["meta_key"]=> string(6) "_liked" ["meta_value"]=> string(2) "10" } }

Upvotes: 1

Views: 149

Answers (1)

M. Eriksson
M. Eriksson

Reputation: 13635

The column meta_value is a varchar, which means that all values (including numbers) are being sorted as strings.

Cast the column as INT when sorting: 

ORDER BY CAST({$wpdb->prefix}postmeta.meta_value AS UNSIGNED) ASC

Upvotes: 3

Related Questions