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

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?

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  );