I am testing something out and I have a query like this:

$paged = 1; 
$filter_min_price = 5;
$filter_max_price = 300;
$products5_cat_term_id = 368;
$filter_brand = "Now, Garmin";
$sort_order = "ASC";

    $the_query = array(
        'post_type' => 'products5',          // name of post type.
        'tax_query' => array(
            array(
                'taxonomy' => 'products5_categories',   // taxonomy name
                'field' => 'term_id',           // term_id, slug or name
                'terms' => $products5_cat_term_id,
            )
        ),
        'posts_per_page' => 100,
        'paged' => $paged,
        'post_status' => 'publish',         
        'meta_query' => array(
            'relation' => 'AND',
            'price_clause' => array(
                'key'     => 'kixx_product_price',
                'value'   => array( $filter_min_price, $filter_max_price ),
                'type'    => 'DECIMAL',
                'compare' => 'BETWEEN',
            ),

            array(
                'relation' => 'OR',
                'brand_clause' => array(
                    'key'     => 'kixx_product_brand',
                    'value'   => $filter_brand,
                    'compare' => 'IN',
                ),
            ),

        ),
        'orderby' => array(
            'price_clause' => $sort_order,
            'brand_clause' => $sort_order,
        ),              

    );
$t5_products_query = new WP_Query($the_query);  

if ($t5_products_query->have_posts()) { 
        while($t5_products_query->have_posts()) : $t5_products_query->the_post();
            $current_post_id = get_the_ID();
            $kixx_product_price = get_post_meta($current_post_id, "kixx_product_price", true);          
            $kixx_product_brand = get_post_meta($current_post_id, "kixx_product_brand", true);
            $kixx_merchant_id = get_post_meta($current_post_id, "kixx_merchant_id", true);          
            echo "$kixx_product_price is: $current_post_id and kixx_product_brand: $kixx_product_brand and kixx_merchant_id: $kixx_merchant_id<br>";

        endwhile;
} else {
    // No products matched criteria
}       

And what I expect to get is products sorted by price as a first condition and then by product_brand (ASC or DESC)...

Why wordpress doesn't sort decimals correctly as I've explicitly placed to treat kixx_product_price as a decimal value but it still won't... ?

Here is the output:

9.94 is: 8763 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.41 is: 5383 and kixx_product_brand: Garmin and kixx_merchant_id: 100452976
15.41 is: 5569 and kixx_product_brand: Garmin and kixx_merchant_id: 100452976
15.16 is: 3485 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.06 is: 3629 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.17 is: 4785 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.17 is: 4865 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.11 is: 5149 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.17 is: 5857 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.17 is: 6973 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.17 is: 7750 and kixx_product_brand: Now and kixx_merchant_id: 100452976
16.36 is: 5269 and kixx_product_brand: Garmin and kixx_merchant_id: 100452976
16.30 is: 4367 and kixx_product_brand: Now and kixx_merchant_id: 100452976

Any help is greatly appreciated !

---

Edit:

I've seen the query and it looks like this:

 SELECT SQL_CALC_FOUND_ROWS  
 {$wpdb->prefix}posts.ID FROM {$wpdb->prefix}posts  
 LEFT JOIN {$wpdb->prefix}term_relationships ON ({$wpdb->prefix}posts.ID = {$wpdb->prefix}term_relationships.object_id) 
 INNER JOIN {$wpdb->prefix}postmeta ON ( {$wpdb->prefix}posts.ID = {$wpdb->prefix}postmeta.post_id )  
 INNER JOIN {$wpdb->prefix}postmeta AS mt1 ON ( {$wpdb->prefix}posts.ID = mt1.post_id ) 
 WHERE 1=1  
 AND ( {$wpdb->prefix}term_relationships.term_taxonomy_id IN (368)) 
 AND ( ( {$wpdb->prefix}postmeta.meta_key = 'kelkoo_product_price' AND CAST({$wpdb->prefix}postmeta.meta_value AS DECIMAL(10,2)) BETWEEN '5' AND '300' ) 
 AND (( mt1.meta_key = 'kelkoo_product_brand' AND mt1.meta_value IN ('Now','Garmin') ))) 
 AND {$wpdb->prefix}posts.post_type = 'products5' 
 AND (({$wpdb->prefix}posts.post_status = 'publish')) 
 GROUP BY {$wpdb->prefix}posts.ID 
 ORDER BY CAST({$wpdb->prefix}postmeta.meta_value AS DECIMAL) ASC, 
 CAST(mt1.meta_value AS CHAR) ASC 
 LIMIT 0, 100

so obviously problem is 3rd line from bottom, where is CAST to DECIMAL instead of DECIMAL(10,2)... When I run query manually I get results as expected, but not sure how to modify WP query to add DECIMAL(10,2) ?

I am testing something out and I have a query like this:

$paged = 1; 
$filter_min_price = 5;
$filter_max_price = 300;
$products5_cat_term_id = 368;
$filter_brand = "Now, Garmin";
$sort_order = "ASC";

    $the_query = array(
        'post_type' => 'products5',          // name of post type.
        'tax_query' => array(
            array(
                'taxonomy' => 'products5_categories',   // taxonomy name
                'field' => 'term_id',           // term_id, slug or name
                'terms' => $products5_cat_term_id,
            )
        ),
        'posts_per_page' => 100,
        'paged' => $paged,
        'post_status' => 'publish',         
        'meta_query' => array(
            'relation' => 'AND',
            'price_clause' => array(
                'key'     => 'kixx_product_price',
                'value'   => array( $filter_min_price, $filter_max_price ),
                'type'    => 'DECIMAL',
                'compare' => 'BETWEEN',
            ),

            array(
                'relation' => 'OR',
                'brand_clause' => array(
                    'key'     => 'kixx_product_brand',
                    'value'   => $filter_brand,
                    'compare' => 'IN',
                ),
            ),

        ),
        'orderby' => array(
            'price_clause' => $sort_order,
            'brand_clause' => $sort_order,
        ),              

    );
$t5_products_query = new WP_Query($the_query);  

if ($t5_products_query->have_posts()) { 
        while($t5_products_query->have_posts()) : $t5_products_query->the_post();
            $current_post_id = get_the_ID();
            $kixx_product_price = get_post_meta($current_post_id, "kixx_product_price", true);          
            $kixx_product_brand = get_post_meta($current_post_id, "kixx_product_brand", true);
            $kixx_merchant_id = get_post_meta($current_post_id, "kixx_merchant_id", true);          
            echo "$kixx_product_price is: $current_post_id and kixx_product_brand: $kixx_product_brand and kixx_merchant_id: $kixx_merchant_id<br>";

        endwhile;
} else {
    // No products matched criteria
}       

And what I expect to get is products sorted by price as a first condition and then by product_brand (ASC or DESC)...

Why wordpress doesn't sort decimals correctly as I've explicitly placed to treat kixx_product_price as a decimal value but it still won't... ?

Here is the output:

9.94 is: 8763 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.41 is: 5383 and kixx_product_brand: Garmin and kixx_merchant_id: 100452976
15.41 is: 5569 and kixx_product_brand: Garmin and kixx_merchant_id: 100452976
15.16 is: 3485 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.06 is: 3629 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.17 is: 4785 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.17 is: 4865 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.11 is: 5149 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.17 is: 5857 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.17 is: 6973 and kixx_product_brand: Now and kixx_merchant_id: 100452976
15.17 is: 7750 and kixx_product_brand: Now and kixx_merchant_id: 100452976
16.36 is: 5269 and kixx_product_brand: Garmin and kixx_merchant_id: 100452976
16.30 is: 4367 and kixx_product_brand: Now and kixx_merchant_id: 100452976

Any help is greatly appreciated !

---

Edit:

I've seen the query and it looks like this:

 SELECT SQL_CALC_FOUND_ROWS  
 {$wpdb->prefix}posts.ID FROM {$wpdb->prefix}posts  
 LEFT JOIN {$wpdb->prefix}term_relationships ON ({$wpdb->prefix}posts.ID = {$wpdb->prefix}term_relationships.object_id) 
 INNER JOIN {$wpdb->prefix}postmeta ON ( {$wpdb->prefix}posts.ID = {$wpdb->prefix}postmeta.post_id )  
 INNER JOIN {$wpdb->prefix}postmeta AS mt1 ON ( {$wpdb->prefix}posts.ID = mt1.post_id ) 
 WHERE 1=1  
 AND ( {$wpdb->prefix}term_relationships.term_taxonomy_id IN (368)) 
 AND ( ( {$wpdb->prefix}postmeta.meta_key = 'kelkoo_product_price' AND CAST({$wpdb->prefix}postmeta.meta_value AS DECIMAL(10,2)) BETWEEN '5' AND '300' ) 
 AND (( mt1.meta_key = 'kelkoo_product_brand' AND mt1.meta_value IN ('Now','Garmin') ))) 
 AND {$wpdb->prefix}posts.post_type = 'products5' 
 AND (({$wpdb->prefix}posts.post_status = 'publish')) 
 GROUP BY {$wpdb->prefix}posts.ID 
 ORDER BY CAST({$wpdb->prefix}postmeta.meta_value AS DECIMAL) ASC, 
 CAST(mt1.meta_value AS CHAR) ASC 
 LIMIT 0, 100

so obviously problem is 3rd line from bottom, where is CAST to DECIMAL instead of DECIMAL(10,2)... When I run query manually I get results as expected, but not sure how to modify WP query to add DECIMAL(10,2) ?

• custom-post-types
• wp-query
• custom-taxonomy

Share Improve this question edited Jun 4, 2019 at 20:11 nmr 4,56722 gold badges1717 silver badges2525 bronze badges asked Jun 4, 2019 at 14:24 PeterPeter 12988 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You can use 'type' => 'DECIMAL(10, 2)', in meta query.

It is mentioned in the documentation Custom_Field_Parameters.