Get a post_id where meta_value equals something in a serialized meta_value field

Question

I'm storing CPT products' post IDs into CPT companies' post meta (meta_key = prefix_products). As a company can have multiple products, so I'm storing them into a PHP serialized array value.

s:48:"a:3:{i:0;s:3:"334";i:1;s:3:"333";i:2;s:3:"331";}";

Where 334, 333, 331 are three post_ids of post type products.

I need to get the post_id of the CPT companies where the product id (post_id of CTP products) is equal to 331 (dynamic - can be any value). It should compare with meta_value of the meta_key prefix_products.

I can get post_id using a meta_value, but I'm stuck where the data is stored as serialized. :(

What I did so far, can't get to the solution:

function get_company_of_the_product( $product_id ) {
    global $project_prefix;
    $prod_meta_key = $project_prefix .'products';

    $companies = get_posts( array(
            'post_type'     => 'companies',
            'post_status'   => 'publish',
            'meta_key'      => $prod_meta_key
        ) );
    $products_array = array();
    foreach ( $companies as $company ) {
        $products = get_post_meta( $company->ID, $prod_meta_key, true );
        $products_array[$company->ID] = unserialize( $products );
        //if( in_array( $product_id, $products_array ) ) return $company->ID; //I know it's wrong
    }

    var_dump($products_array);
}

Is this the way, or am I missing something seriously easy? How can I solve the riddle?

Answer 1

I would strongly suggest to separate the products and not to put them all into one array. Or even to create a taxonomy companies, but this is more up to the while design and maybe a bad idea. Nevertheless this is a nice question to solve, so let's play.

So, the 'prefix_products' meta key is always an array of products. Now you need all companies, which sell a product with a certain ID.

This should do the trick:

function get_all_companies_selling( $product_id ){
    global $wpdb;
    $sql = "select post_id from " . $wpdb->prefix . "postmeta where
        meta_key = 'prefix_products' &&
        meta_value like '%%%s%%'";

    $product_id = 's:' . strlen( $product_id ) . ':"' . (int) $product_id . '";';
    $sql = $wpdb->prepare( $sql, $product_id );
    $res = $wpdb->get_results( $sql );

    return $res;
}

get_all_companies_selling( 331  );

Answer 2

It's not possible, you have to store that value separately, and then you'll be able to use it.

Answer 3

This is very possible though possibly slow. It's essentially a reverse relationship search and is common with Advanced Custom Fields or any other custom field used for relationships.

The following will get not just the IDs, it returns an array of posts object via WP_Query.

$some_product_id = '123';
$args= array(
  'post_type' => 'companies',  // only search postmeta of these cpts
  'posts_per_page' => 5,  // limit or not depending on your use
  'orderby' => 'date',  // use whatever order you like
  'meta_query' => array(
    array(
    'key' => 'prefix_products', // name of custom field
    'value' => '"' . $some_product_id . '"', // matches exactly "123" not just 123.
    'compare' => 'LIKE'
  )
);

$the_related_companies = new WP_Query( $args );

if( $the_related_companies->have_posts() ) :
  <div class="container">
  while( $the_related_companies->have_posts() ) :
    [do stuff with your posts]
  endwhile;
  </div>
endif;

(Excuse my PHP, I've been working in Blade a lot and it's hard to go back)