WebDevDanno WebDevDanno - 5 months ago 16
SQL Question

Use get_post_meta array as meta_query value

I am trying to query the meta data

_players
in my database which has serialized arrays as values the data is inserted via a wordpress meta box.

I thought I could just do a
LIKE
comparison on a serialized version of the
get_post_meta
but the results only show posts with that exact serialized string.

Get the meta data

$keyGUID = serialize(get_post_meta($postID, "_players", true));
// returns "a:2:{i:0;s:8:"DC242003";i:1;s:8:"BY523643";}"


Build query arguements

$types = get_post_types();
$args = array(
"post_type" => $types,
"post_status" => "publish",
"posts_per_page" => $posts,
"meta_query" => array(
array(
"key" => "_players",
"value" => $keyGUID,
"compare" => "LIKE"
)
)
);
$myposts = get_posts($args);


And the database records I am querying

enter image description here

How do I modify my script to process the post meta so it queries each GUID?

What I want to happen is for posts to be displayed if either of the values exist not if they both exist

1 Post for BY523643

2 Posts for DC242003

Answer

You can query serialized strings with LIKE but you need to break the IDs out and search with those as parameters.

$players = get_post_meta( $postID, "_players", true );

// to get any posts that have either player
$types = get_post_types();
$args = array(
    "post_type" => $types,
    "post_status" => "publish",
    "posts_per_page" => $posts,
    "meta_query" => array(
        'relation' => 'OR',
    )   
);

foreach ( $players as $player_id ) {
    $args['meta_query'][] = array(
        "key" => "_players",
        "value" => $player_id,
        "compare" => "LIKE"
    );
}


$myposts = get_posts( $args );

// to get posts per player
foreach ( $players as $player_id ) {
    $args = array(
        "post_type" => $types,
        "post_status" => "publish",
        "posts_per_page" => $posts,
        "meta_query" => array(
            array(
                "key" => "_players",
                "value" => $player_id,
                "compare" => "LIKE"
            )
        )   
    );

    $myposts = get_posts( $args );

    // do something with the posts for this player
}

One of the downsides with using LIKE on serialized strings it that sometimes you get unwanted results. For example you have a player with the ID 12345 and player with the ID 123. If you are searching for 123 you would get both players. But if all your IDs are unique and the same amount of characters you likely won't run into that problem.