CODEWITHSUNDEEP
phpmysqlsortingwordpress
lukeseager
lukeseager

Reputation: 2615

WP_Query sort by 'meta_value_num' on string

I need to allow users to be able to sort the results of a wp_query by a few custom field values. These include Distance, Date and Price. Distance and Date are working fine, but Price returns no results at all!

I think my issue is that the custom field I'm sorting by is a text field, and I'm trying to sort by numeric value. However, if I set the field to a numeric field, I can't add decimals to the number, can I?

So, does anyone know a way of getting a wp_query to order by numeric value when the custom field being used is a text field?

Here's my query incase it helps:

$args = array(
    // general
    'post__in' => $postIDs,
    'post_type' => 'event',
    'posts_per_page' => 10,
    'paged' => $paged,

    'meta_key' => 'adult',
    'orderby' => 'meta_value_num',
    'order' => 'ASC',

    // category filter
    'tax_query' => $taxQuery,

    // date filter
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key' => 'date_%_start-date',
            'value' => $when,
            'compare' => '>=',
            'type' => 'NUMERIC'
        ),
        array (
            'key' => 'date_%_end-date',
            'value' => $when2,
            'compare' => '<=',
            'type' => 'NUMERIC'
        )
    )


);  

$temp = $wp_query;
$wp_query = null;
$wp_query = new WP_Query( $args );

EDIT

After the below answer, I could edit the SQL Query to use CAST to make the meta value an integer. However, I'm not too sure about the syntax, here's what I'm thinking at the moment, but it doesn't seem to work:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID 
FROM wp_posts  
INNER JOIN wp_term_relationships 
ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) 
WHERE 1=1  
AND wp_posts.ID IN (66864,44870, ... more numbers ... ,66261,31361) 
AND ( wp_term_relationships.term_taxonomy_id IN (2) ) 
AND wp_posts.post_type = 'event' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND (CAST(wp_postmeta.meta_key = 'adult' AS UNSIGNED) // CAST attempt here...
AND  (mt1.meta_key LIKE 'date_%_start-date' AND CAST(mt1.meta_value AS SIGNED) >= '20140619')
AND  (mt2.meta_key LIKE 'date_%_end-date' AND CAST(mt2.meta_value AS SIGNED) <= '20140626') ) 
AND substr(wp_postmeta.meta_key, 1, 6) = substr(mt1.meta_key, 1, 6)  
GROUP BY wp_posts.ID 
ORDER BY wp_postmeta.meta_value+0 ASC 
LIMIT 0, 10

EDIT

Attempting to use float and ACF update_field to change the value to an integer, but code doesn't seem to do anything for some reason:

function update_adult_field( $value, $post_id, $field  ) {
    $value = (float) $value;
    return $value;
}
add_filter('acf/update_value/name=adult', 'update_adult_field', 10, 3);

Upvotes: 4

Views: 3697

Answers (1)

henrywright
henrywright

Reputation: 10240

if I set the field to a numeric field, I can't add decimals to the number, can I?

I think floatval() could be of some use to you here. It allows you to get the float value of a string.

$my_var = '1.3142';
$float_value_of_my_var = floatval( $my_var );

$float_value_of_my_var will hold a numeric value.

Alternatively you can cast you variable as a float

(float) $my_var;

Ref: http://www.php.net//manual/en/function.floatval.php

Upvotes: 3

Related Questions