wordpress - Get Posts based on serialized meta value

Question

I have a series of posts and all have a meta_key with the value of "owner" and meta_value with serialized data such as "a:3:{i:0;s:3:"325";i:1;s:2:"41";i:2;s:2:"29";}"

meta_key owner

meta_value a:3:{i:0;s:3:"325";i:1;s:2:"41";i:2;s:2:"29";}

I am trying to figure out how to properly use get_posts() to return all the posts that have a meta_key with the value of owner and the meta_value containing a specific value such as 41

    $args = array(
        'post_type' => 'rp_applications',
        'nopaging' => true,
        'meta_query' => array(
            'relation' => 'AND',
            array(
                'compare' => '=',
                'key' => 'archived',
                'value' => '0000-00-00'
            ),
            array(
                'compare' => '=',
                'key' => 'owner',
                'value' => ???? WHAT DO I PUT HERE ????
            )
        )
    );

    $applications = get_posts($args);

question from:https://stackoverflow.com/questions/65866020/get-posts-based-on-serialized-meta-value

Answer 1

This sounds like you should be storing the "Owner" as a taxonomy term instead of meta data. It would give you easier and faster querying abilities as well.

That said, it's definitely possible to do what you want. I'd be cautious though, because the postmeta table by default is not indexed, so a large table with meta queries run against it will slow down your site quite a lot. You may want to consider adding a partial index to the table if it's going to be even remotely large (or again, switch to Taxonomy Terms instead of post meta for this).

If you still care to use a meta query, take a look at the compare options available in the WP_Meta_Query() docs.

One of the available options is REGEXP, so you could do something like:

$args = array(
    'post_type' => 'rp_applications',
    'nopaging' => true,
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'compare' => 'REGEXP',
            'key'     => 'owner',
            'value'   => sprintf( 'a:d:{i:0;s:d:"d.*?";i:1;s:%d:"%d".*', strlen($owner_id), $owner_id )
        ),
        array(
            'compare' => '=',
            'key'     => 'archived',
            'value'   => '0000-00-00'
        )
    )
);

Of course this method only works if the serialized data is in the same format on each one, and you'd need to adjust the regular expression if not - but that should be enough to get you started!