CODEWITHSUNDEEP
phpmysqlsqlwordpressadvanced-custom-fields
aiddev
aiddev

Reputation: 1429

`ORDER BY meta_value ASC` in sql query is not sorting prices correctly

I'm using wordpress, have products in the website and product price is a custom field in the backend.

I have a page where products should be sorted by price from high to low and vice versa.

My Sql query looks like this:

SELECT * FROM `wp_postmeta` WHERE meta_key = 'product_price' ORDER BY meta_value ASC

Above query is not sorting prices from low to high.

But when I try to do it in below way, it works perfectly:

$args = array(
    'post_type' => 'product-items',
    'meta_key' => 'product_price',
    'orderby' => 'meta_value',
    'order' => 'ASC',
    'post_status' => 'publish',
    'posts_per_page'=> -1
);
$productList = get_posts($args);

In the database, I see that meta_value column type is longtext. Maybe this is the issue why it is not sorting properly. But how come it works in second way.

I would go with second option but my code requires to do it via sql query depending on some other things.

Upvotes: 1

Views: 1798

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

meta_value is a string -- that is the nature of EAV models.

In MySQL, I like to use silent conversion for this. That is, just treat the value as a numeric and do arithmetic:

ORDER BY (meta_value + 0) ASC

The advantage of silent conversion is that it does not generate errors for non-numeric values.

Upvotes: 2

Elvin Haci
Elvin Haci

Reputation: 3572

Yes, you are right. Meta_value is not numeric, that's why it can't sort it by default. You can write meta_value*1 as juergen d wrote above. Or you can use SQL CAST for that:

SELECT * FROM `wp_postmeta` 
WHERE meta_key = 'product_price' 
ORDER BY CAST(meta_value as unsigned) ASC

Upvotes: 1

Related Questions