Reading Content in an Array as it relates to a meta_query

Question

I am capturing a user's Work Anniversary and their content is being stored in an array. So the key as it relates to these Meta Fields is hire_date and the value that is stored in that array looks something like this a:2:{s:3:"day";s:1:"8";s:5:"month";s:5:"March";}

What I don't really understand is how or what the 'orderby' => 'meta_value', is actually sorting by as the key has multiple values.

Right now if have the following code and I would like to know that the ASC order relates to the $current_day but I don't believe it is. I have tried using the 'type' => 'NUMERIC' and the 'orderby' => 'meta_value_num' but frankly I'm just not sure how to deal with this issue to display these work anniversary in ascending order in the month.

Thanks for any help solving this issue!

$current_month = date('F');
$current_day = date('d');
$args_anniversary_current = array(
'role__in' => array('subscriber', 'subscriberlimited'),
'order' => 'ASC',
'orderby' => 'meta_value',

'meta_query' => array(

        array(
            'relation'  => 'AND',

            array(
                'key'       => 'hire_date',
                'value'     => $current_month,
                'compare'   => 'LIKE',
            ),
            array(
                'key'       => 'hire_date',
                'value'     => $current_day,
                'compare'   => '>=',
            ),
        ),
    ),
);

Answer 1

What I don't really understand is how or what the 'orderby' => 'meta_value', is actually sorting by as the key has multiple values.

It doesn't have multiple values. It has a single value:

a:2:{s:3:"day";s:1:"8";s:5:"month";s:5:"March";}`

This is a 'serialised array'. This is just a way to store an array as a string. As far as WordPress (and MySQL) are concerned, this is just a string that starts with the letter a, and it will be sorted accordingly.

The fact is that serialised arrays are very poor ways of storing data if you need to query by them, or sort by them.

You're also storing the Month name, which means that you can't easily sort by month, because the month names aren't alphabetical.

If you want to sort and query by a month and day with WP_Query or WP_User_Query, you'd be better off storing the hire date as 2 fields: hire_month and hire_day, and store both those values as numbers. If you did that you could do what you're trying to do like this (I'm guessing that you're trying to display users who have their anniversary this month):

$current_month = date( 'n' ); // 1-12
$current_day   = date( 'j' ); // 1-31

$args_anniversary_current = [
    'role__in'   => [ 'subscriber', 'subscriberlimited' ],
    'order'      => 'ASC',
    'orderby'    => 'hire_day',
    'meta_query' => [
        [
            'relation'  => 'AND',
            'hire_month' => [
                'key'     => 'hire_month',
                'value'   => $current_month,
                'compare' => '=',
            ],
            'hire_day' => [
                'key'     => 'hire_day',
                'value'   => $current_day,
                'compare' => '>=',
            ],
        ],
    ],
]