0

For each user on my site I store a birthday using ACF date field (stored as YYYYMMDD). Is there a way to construct a meta query to retrive all users that their birthday on that specific month regardless of the year?

4
  • It would be significantly faster to also store the month as a term in a private taxonomy. This would allow a much simpler query that would be hundreds of times faster than relying purely on post meta. What you've requested may be functional but the query will get slower and slower as the number of posts increases
    – Tom J Nowell
    Jul 23, 2020 at 14:32
  • I see what you mean, but I am talking about a user query and not posts query, so a taxonomy is not relevant for this
    – Dor
    Jul 23, 2020 at 14:38
  • That is not true, users can have taxonomies too, all object types can have taxonomies
    – Tom J Nowell
    Jul 23, 2020 at 14:39
  • I was not aware of this. I'll look into it. Thanks
    – Dor
    Jul 23, 2020 at 14:42

2 Answers 2

1

While the regexp solution will function, it quickly gets slower as the number of users increases. This is because the database cannot perform optimisation and indexing tricks, and forces a full table scan. The more users, and the more meta, the greater the cost of the query. At a minimum it requires caching to avoid a worst case scenario.

This is true of all meta, be it post meta, comment meta, term meta or user meta. Searching for things by the contents of their meta is super expensive.

But it can be avoided.

As well as storing the data in a user meta key/value pair, register a private/non-public custom taxonomy. When the user saves their profile, set a term on that user in this taxonomy using the month as a value.

Then, you can use an ultra fast query to get users with that month.

First, register the private user taxonomy:

function hidden_user_taxonomies() {
    register_taxonomy( 'birthday_months', 'user', [
        'label'        => __( 'Birthday Months', 'textdomain' ),
        'public'       => false
        'hierarchical' => false,
    ] );
}
add_action( 'init', 'hidden_user_taxonomies', 0 );

Notice the object type here is not a post type, but user, and that public is set to false.

Then, we hook into user profile save, and set the term for that user:


add_action( 'personal_options_update', 'save_hidden_birthday_month' );
add_action( 'edit_user_profile_update', 'save_hidden_birthday_month' );

function save_hidden_birthday_month( $user_id ) {
    $birthday = get_field( 'birthday field name' );
    $month = ... get the birthday month out of the string....
    wp_set_object_terms( $user_id, [ strval( $month ) ], 'birthday_months', false );
}

Now we have a hidden user taxonomy that contains terms for each month of the year, and users are assigned to those months when their profile is updated. Also notice the intval call, this is so we store 4 rather than 04.

So how do we fetch the users? Simples:

function users_in_birthday_month( $month ) : array {
    // fetch the month term
    $month = get_term_by( 'name', $month, 'birthday_months' ); // who has birthdays in July?
    if ( !$month ) {
        // nobody has a month in July as there is no term for July yet
        return [];
    }
    // fetch the user IDs for that month
    $user_ids = get_objects_in_term( $month->term_id, 'birthday_months' );
    if ( empty( $user_ids ) ) {
        // there were no users in that month :(
        return [];
    }
    $user_query = new WP_User_Query( [
        'include' => $user_ids,
    ] );
    return $user_query->get_results();
}

So to get all users with a birthday in July?

$july_birthdays = users_in_birthday_month( 7 );
foreach ( $july_birthdays as $user ) {
    // output user display name
}

And if you want to display the full birthday, just fetch the original post meta field.

This means:

  • You can now use ultra fast taxonomy queries!
  • Your query will not get exponentially slower as your userbase grows
  • This data is very cacheable, and will see more performance gains if an object cache is in use
  • There are only 12 possible queries to cache!
  • You could extend this to include days of the month, years, etc
  • It may be necessary to fetch the month value from $_POST rather than get_field as ACF may not have saved the value yet

Remember, don't use meta to search for things, that's what terms and taxonomies were created for.

Posts aren't the only things that can have taxonomies and meta, for more information on user taxonomies, see this helpful article from Justin Tadlock.

1
  • I see the benefis of you approch. It took me a while to implement is as I coukd not get it to save the term when updating the user. I finally realized that the problem was that the 2nd argument of wp_set_object_terms was an array containing one int. once i casted it as string it worked like a charm. eventually what I did was that: function save_hidden_birthday_month( $user_id ) { $birthday = get_field( 'birthday field name' ); $month = intval(MY MONTH) wp_set_object_terms( $user_id, [ strval( $month ) ], 'birthday_months', false ); }
    – Dor
    Jul 25, 2020 at 13:08
0

If that field is definitely stored in a string like: 20000101 then you can use the REGEXP compare method to do what you want.

The code below uses the WP_Query meta fields to make a regex which takes any character for the year and day, and matches a particular month.

You'd need to give this a go on your data, but it would look something like this:

$monthNumber = 6; // e.g. June
// ensure always two characters, so June will become "06"
$monthNumberString = str_pad($monthNumber, 2, "0", STR_PAD_LEFT); 
// regex to only match month
$matchMonthRegex = '....' . $monthNumberString . '..'; 

$args = array(
    'meta_key' => 'YOUR_BIRTHDAY_META_FIELD_NAME_HERE',
    'meta_value' => $matchMonthRegex,
    'meta_compare' => 'REGEXP'
);

$result = WP_Query($args);

Let me know if that works?

3
  • The approach is great, I did not think about regex comparison. There was a problem with the syntax though. Once I changed it to following correct syntax it worked. $matchMonthRegex = '....' . $monthNumberString . '..'; Thanks for the directions
    – Dor
    Jul 23, 2020 at 14:24
  • This is a post query, the OP asked for a user query
    – Tom J Nowell
    Jul 23, 2020 at 14:40
  • @Dor oops, well caught, yes I totally meant . and will update the answer
    – mozboz
    Jul 23, 2020 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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