get posts based on non-single metadata

Question

I have meta data to custom post "event", registered as non-single, i.e., there is an array od metadata "terms" (it is not WordPress term, I just call it term, it is a record containing start date of the event, end date of event and possibly start and end time of the event.

register_meta(
    'post',
    'term_definition',
    [
        'object_subtype' => 'event',
        'type' => 'object',
        'description' => 'object with time data of the event',
        'single' => false,
        'auth_callback' => __NAMESPACE__ . '\authentication',
        'sanitize_callback' => 'sanitization',
        'show_in_rest' => [
            'schema' => [
                'type' => 'object',
                'properties' => [
                    'ID' => [
                        'type' => 'integer'
                    ],
                    'startDate' => [
                        'type' => 'string'
                    ],
                    'endDate' => [
                        'type' => 'string'
                    ],
                    'startTime' => [
                        'type' => 'string'
                    ],
                    'endTime' => [
                        'type' => 'string'
                    ],
                ]
            ]
        ]
    ]
)

I want to get all posts of my custom type event, where startDate in metadata is after current datetime (or before current datetime). From documentation, it seems this is only possible for single, simple metadata, as described in the handbook. As a sidenote, I noticed that metadata created in this way get serialized and stored in the database like so:

meta_key: 'term_definition'
meta_value: a:3:{s:9:"startDate";s:19:"2021-12-09T16:36:00";s:2:"ID";i:0;s:7:"endDate";s:19:"2021-12-10T16:36:00";}

Answer 1

You are correct, this is not possible for serialised data. There are some hacks to try and get around this by searching for individual parts of the serialised string, but those throw up lots of false positives and can't be sorted.

Generally, it's an anti-pattern to store structured data inside a single meta value, and you can't query the internal data in a meta_query field. If you absolutely must store it, store it as JSON, but keep in mind you can't query or perform logic on the contents. ( serialised PHP values also open you up to object deserialisation attacks, and break when naive search replace queries are ran on the database if the length changes )

The solution here is to separate it out into individual atomic post meta, at least 5, do the sorting in PHP/JS,or to create child post types to represent your events so that you can use the standard date time columns