1

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) ?

0

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

It is mentioned in the documentation Custom_Field_Parameters.

  • I did it with function: add_filter('get_meta_sql','cast_decimal_precision'); function cast_decimal_precision( $array ) { $array['where'] = str_replace('DECIMAL','DECIMAL(10,2)',$array['where']); } and it does work on line 8 - thing is that it has to repopulate this line as well ORDER BY CAST({$wpdb->prefix}postmeta.meta_value AS DECIMAL) ASC, – Peter Jun 4 at 16:13
  • You see what I mean this: ORDER BY CAST({$wpdb->prefix}postmeta.meta_value AS DECIMAL) ASC," has to be: "ORDER BY CAST({$wpdb->prefix}postmeta.meta_value AS DECIMAL(10,2)) ASC," – Peter Jun 4 at 16:16
  • 1
    If you disable your get_meta_sql filter and change type to 'DECIMAL(10,2)' in 'price_clause', proper cast should appear in ORDER BY. – nmr Jun 4 at 16:51
  • And that's true... It's working when I remove it from filter via function and place it directly.... Gee.... – Peter Jun 4 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.