Trouble with serialized metadata

Question

I've read about 20 different posts on this topic and I still can't apply the solutions to the problem I'm having. I'm very sorry for not being able to figure it out based on those answers, but I wonder if someone could help me with my particular situation. I'm a beginner at php, so please forgive my ignorance.

I want to create a query on a post which displays other posts where two meta key values match (with an OR relationship) the post ID of the current post.

My setup is as follows:

• Post meta field 'author_company' - Stores the details of another post as serialized data, e.g. "a:24:{s:2:""ID"";s:3:""531"";s:11:""post_author"";s:2:""44"";s:9:""post_date"";s:19:""2015-12-18 10:35:43"";s:13:""post_date_gmt"";s:19:""2015-12-18 10:35:43"";s:12:""post_content"";s:581:""...
• Post meta field '_pods_author_company' - This was created by the Pods plugin I'm using for CPTs, and it stores just the post ID but still in a serialized format, e.g. a:1:{i:0;i:531;}

I have a post acting as company profile page, where I want to display all the posts which have been assigned to that company. How can I find posts where 'author_company' OR '_pods_author_company' contains the ID matching this company profile post?

E.g. if Example Company Ltd. has a post ID of 444, and there are some posts which have been assigned to that company and therefore the serialized data for 'author_company' and/or '_pods_author_company' on those posts contain the ID 444, how do I display those posts?

Here's how I can get the data I need:

On the company profile post:

$post_id = get_the_ID(); //Get the ID of the current post (which is the company profile post)    

On the posts assigned to that company:

$author_company = get_post_meta( $post->ID, 'author_company', true ); 
$author_company_id = $author_company['ID']; //Get just the post ID from the serialized data
    
$_pods_company = get_post_meta( $post->ID, '_pods_author_company', true );
$_pods_company_id = $_pods_author_company['0']; //Get just the post ID from the serialized data

I have tested with var_dump and those do indeed result in just the ID I need.

To summarise: How can I query posts where $author_company_id OR $_pods_company_id MATCHES the current $post_id?

I know it's very bad practice to use serialized data for meta values, but I don't know how to get around it - it's how Pods works. It's looking like I'm going to have to bin Pods and try and do this another way, but I really don't want that to be the case...

Thanks for any assistance you can provide.

Answer 1

For 99% of cases, no, you can't.

Serialised data is not something you can search, and that is the fundamental problem. Not how to search it, but that it was serialised to begin with. Serialising is unnecessary. This is for the same reasons you cannot unscramble an egg, serialised data, especially PHP serialised data, is not built to be searched, neither is the database optimised for it.

So, the solution is to store the data you which to search for as independent post meta that can be searched. Or better yet, avoid ultra expensive post meta queries entirely and store the data you want to search by in taxonomy terms ( as was originally designed for and intended ).

For that 1% of situations, you might be able to use a LIKE type search looking for the value if it's already known, but you run the risk of false positives, and, this will be a super slow/expensive/heavy query. If it works.

In future, there are some important pieces of information that will assist you:

• Meta keys are not unique, you can add multiple values with the same meta key via add_post_meta, then retrieve them as an array with get_post_meta by setting the 3rd parameter to false. You don't need serialisation for repeatable fields.
• You don't need serialised strings to store structured arrays. Using 3 meta key/value pairs called company_name company_id and company_address is far better than a single company meta with a serialised structure that has id name and address internal fields

I know it's very bad practice to use serialized data for meta values, but I don't know how to get around it - it's how Pods works. It's looking like I'm going to have to bin Pods and try and do this another way, but I really don't want that to be the case...

1. You don't have to use Pods for everything, you can use Pods and then use an extra plugin library or manually written code
2. Pods support and documentation might have answers for this, but 3rd party plugin support is off-topic on this site.
3. Even if Pods cannot be changed, that doesn't prevent you saving additional duplicated data via filters and hooks that can be queried against.
4. Use a taxonomy for things you want to filter/query/search for. Post meta is optimised for finding key/values when you already know the post ID. Turning that upside down means the cost of the query gets big very quickly, and does not scale. It's one of the biggest causes of slowness on WordPress sites.