customization - Get users based on month ACF datepicker field

admin管理员组

文章数量:1320915

I have an ACF field 'date_of_birth' which stores the user's birthday, examples: 20201226, 20151225, 19980701

Goal: get all users which have their birthday this month, or next month.

I currently have this:

    $users_start_date_boundary = gmdate( 'Ymd', strtotime( '-1 month' ) );
    $users_end_date_boundary   = gmdate( 'Ymd', strtotime( '+2 month' ) );

    $wp_user_query = new WP_User_Query(
        [
            'fields'     => 'all_with_meta',
            'orderby'    => 'date_of_birth',
            'order'      => 'ASC',
            'meta_query' => [
                [
                    'key'     => 'date_of_birth',
                    'value'   => [ $users_start_date_boundary, $users_end_date_boundary ],
                    'compare' => 'BETWEEN',
                    'type'    => 'DATE',
                ],
            ],
        ]
    );

    return $wp_user_query->get_results();

The problem with this however, that it only gets users with a birthyear of 2020.

How do I get all users, based on the given month boundaries, while ignoring the year/day the person is born in?

I have an ACF field 'date_of_birth' which stores the user's birthday, examples: 20201226, 20151225, 19980701

Goal: get all users which have their birthday this month, or next month.

I currently have this:

    $users_start_date_boundary = gmdate( 'Ymd', strtotime( '-1 month' ) );
    $users_end_date_boundary   = gmdate( 'Ymd', strtotime( '+2 month' ) );

    $wp_user_query = new WP_User_Query(
        [
            'fields'     => 'all_with_meta',
            'orderby'    => 'date_of_birth',
            'order'      => 'ASC',
            'meta_query' => [
                [
                    'key'     => 'date_of_birth',
                    'value'   => [ $users_start_date_boundary, $users_end_date_boundary ],
                    'compare' => 'BETWEEN',
                    'type'    => 'DATE',
                ],
            ],
        ]
    );

    return $wp_user_query->get_results();

The problem with this however, that it only gets users with a birthyear of 2020.

How do I get all users, based on the given month boundaries, while ignoring the year/day the person is born in?

• customization
• custom-field
• filters
• advanced-custom-fields
• date

Share Improve this question edited Nov 10, 2020 at 20:42 vancoder 7,9242828 silver badges3535 bronze badges asked Nov 10, 2020 at 13:34 fanatic21221fanatic21221 1 11

• WP / the db could have a problem with the format the dates is in. Can you instead save it as a timestamp? Or well formatted (i.e. 2020-12-26)? – kero Commented Nov 10, 2020 at 14:20
• 1 @kero ACF doesn't allow database configurations, I'll have to deal with the current format. I can't save it as a timestamp, but I can use a date time picker instead of a datepicker if that would help – fanatic21221 Commented Nov 10, 2020 at 14:40
• You definitely need a more complex code to do what you want, and I have an idea what is needed to be done. Unfortunately (or fortunately :) ) I don't use ACF with any of my sites so to test it I need to know how exactly this meta field appeared in the database. Can you attach an example to you question? – Ivan Shatsky Commented Nov 10, 2020 at 15:07
• 1 I think you're going to have to write your own SQL query to do this, rather than using WP_User_Query. You probably want the MONTH() function to extract the month field from the date. However I don't think the meta value will be stored as a MySQL date, so maybe you might have to use a substring match? Either way I'd start by getting the query working in SQL. – Rup Commented Nov 10, 2020 at 17:31
• 1 @IvanShatsky Alternatively, you can just store the month in an additional meta field using the acf/update_value hook and then use the >= and <= meta query on that field. Btw the meta values should look like this – kero Commented Nov 10, 2020 at 18:22

 |  Show 6 more comments

2 Answers 2

Sorted by: Reset to default 1

Here is the code based on custom SQL query as suggested by @Rup that should work for you:

// get '-1 month' and '+2 month' dates as an array('YYYY', 'MMDD')
$before = str_split( gmdate( 'Ymd', strtotime( '-1 month' ) ), 4 );
$after = str_split( gmdate( 'Ymd', strtotime( '+2 month' ) ), 4 );
// if the before/after years are the same, should search for date >= before AND <= after
// if the before/after years are different, should search for date >= before OR <= after
$cmp = ( $before[0] == $after[0] ) ? 'AND' : 'OR';
// SQL query
$users = $wpdb->get_col( $wpdb->prepare(
    "SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key = %s AND (SUBSTRING(meta_value, 5, 4) >= '%s' %s SUBSTRING(meta_value, 5, 4) <= '%s')",
    'date_of_birth', $before[1], $cmp, $after[1]
));

Update

Maybe I misunderstand your question looking at your code. The above code would give the list of users whose birthday passed no more than month ago or will happen no more than two months after the current date. To get the list of all users which have their birthday this month or next month, use the following code:

$current = gmdate( 'm' );
$next = sprintf( "%02d", $current % 12 + 1 );
$users = $wpdb->get_col( $wpdb->prepare(
    "SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key = %s AND (SUBSTRING(meta_value, 5, 2) = '%s' OR SUBSTRING(meta_value, 5, 2) = '%s')",
    'date_of_birth', $current, $next
));

I believe since ACF stores the values as Ymd you have to write some kind of workaround. One possible solution could be that you hook into saving the field and additionally store the month in another meta field. This would allow to query for => and <= with the meta query.

Another solution is to not store the date in an arbitrary format but one that the database understands and then using the database's existing functions.

\add_filter('acf/update_value/type=date_picker', function($value) {
    if (empty($value)) {
        return $value;
    }

    $dt = \DateTime::createFromFormat('Ymd', $value);
    return $dt->format(\DateTime::ATOM);
});

\add_filter('acf/load_value/type=date_picker', function($value) {
    if (empty($value)) {
        return $value;
    }

    $dt = new \DateTime($value);
    return $dt->format('Ymd');
});

These two filters will change any date picker to store the date in a well formed date string (like 2005-08-15T15:52:01+00:00) which can easily be understood by the database. You can also just target this specific field, read more about it (doc for acf/update_value and doc for acf/load_value). (Attention! If you already have dates saved in the db, you should manually transform them to new format before applying this code change!)

To retrieve the user IDs you can use $wpdb with a custom query like so

$month = date('m');
$userIds = $wpdb->get_col($wpdb->prepare(
    "SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key = %s AND (MONTH(meta_value) = %d OR MONTH(meta_value) = %d)",
    'date_of_birth',
    $month,
    ($month % 12) + 1
));

(($month % 12) + 1 will get the next month, see this explanation)

It seems that WP_User_Query is not able to retrieve a list of users by their ID, so you would need to call get_userdata() for each user ID, which is probably not optimal - but that depends on your use case.

本文标签： customizationGet users based on month ACF datepicker field