0

I have a custom meta field 'bhaa_runner_dateofrenewal' which records the date that a User on my system last renewed their membership, the field format is YYYY-MM-DD. I'm trying to write a custom query which will query and order the list of returned users by year.

I came across this custom-field-multiple-clauses post which outlines how the 'date()' function can be used in the value field

$args = array(
    'posts_per_page' => 100,
    'post_type' => 'create_events',
    'meta_query' => array(
            'relation' => 'AND',
            array(
                'key' => 'startDate',
                'value' => date('Y-m-d'),
                'compare' => '>='
            ),
            array(
                'key' => 'endDate',
                'value' => date('Y-m-d'),
                'compare' => '<='
            ),
    'orderby'  => 'meta_value', 
    'meta_key' => 'startDate',
    'order' => 'DESC'
    )
);
$custom_query = new WP_Query( $args );

My custom query looks like this at the moment but it is using a String LIKE comparison of the 'bhaa_runner_dateofrenewal' field value.

$usersByCompanyArgs = new WP_User_Query(
    array(
        'fields' => 'all_with_meta',
        'orderby' => 'bhaa_runner_dateofrenewal',
        'order' => 'DESC',
        'meta_query' => array(
            array(
                'key' => 'bhaa_runner_company',
                'value' => $this->houseid,
                'compare' => '='),
            array(
                'key' => 'bhaa_runner_dateofrenewal',
                'value' => '2014',
                'compare' => 'LIKE')
        )
    )
);

Does anybody know how i might update the clause to that i can treat the field as a real date, and use the custom MYSQL YEAR() function here?

1

You can set the type of the data in your meta_query.

Unfortunately, you can only use the comparison BETWEEN if your date is in the format YYYYMMDD. So to see the users for one year, you would have to set your metaquery like this:

$usersByCompanyArgs = new WP_User_Query(
    array(
        'fields' => 'all_with_meta',
        'orderby' => 'bhaa_runner_dateofrenewal',
        'order' => 'DESC',
        'meta_query' => array(
            array(
                'key' => 'bhaa_runner_company',
                'value' => $this->houseid,
                'compare' => '='),
            array(
                'key' => 'bhaa_runner_dateofrenewal',
                'value' => '2014-01-01', // date to compare to, before this one
                'compare' => '<',
                'type' => 'DATE' //set the format
            ),
            array(
                'key' => 'bhaa_runner_dateofrenewal',
                'value' => '2013-01-01', // date to compare to, after this one
                'compare' => '>=',
                'type' => 'DATE' //set the format
            ),
        )
    )
);

If your Date field was formated YYYYMMDD you could replace the two comparisons in the meta_query with

            array(
                'key' => 'bhaa_runner_dateofrenewal',
                'value' => array( '20130101', '20140101' ), // date to compare to, after this one
                'compare' => 'BETWEEN',
                'type' => 'DATE' //set the format
            )

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.