The case of the misbehaving wp e-commerce price sort

So we had an interesting issue where sorting product by price was not working correctly for wp e-commerce products.

The sorting was happening but MySQL was sorting the wp-postmeta.meta_value as a string, so 12.95 was coming AFTER 123.

The solution is nice and simple

$query->set('orderby','meta_value_num');
$query->set('order','ASC');

This way the MySQL does this:

was: ORDER BY wp_postmeta.meta_value
new: ORDER BY wp_postmeta.meta_value+0

Note the difference? The +0 forces MySQL to treat meta_value as a number and sort by that.

This tip comes from Tom @ http://www.tomandvez.com!