Utilizzo di $wpdb per interrogare post con meta_value contenente il post_id corrente

Nel codice che hai pubblicato non recuperi una 'lista di post' come dici, ma recuperi una lista di righe nella tabella meta. Se vuoi recuperare realmente una lista di post utilizza WP_Query con il parametro meta_query.

Qualcosa come:

$id = '10'; // valore unserializzato

$args = array(
  'post_type' => 'post',
  'post_status' => 'publish',
  'posts_per_page' => -1,
  'meta_query' => array(
    array(
      'key' => 'roles_%_production',
      'value' => $id,
      'compare' => 'LIKE'
    )
  )
);
$query = new WP_Query( $args );
$rows = $query->get_posts();

Se vuoi usare $wpdb (non so perché) la query corretta è qualcosa tipo:

<?php
$id = '10'; // valore unserializzato

global $wpdb;
$rows = $wpdb->get_col( $wpdb->prepare(
  "SELECT DISTINCT $wpdb->posts.ID FROM $wpdb->posts, $wpdb->postmeta
  WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id AND
  $wpdb->posts.post_status = 'publish' AND
  $wpdb->posts.post_type = 'post' AND
  $wpdb->postmeta.meta_key = %s AND
  meta_value = %s",
  'roles_%_production',
  $id
) );
?>

$rows conterrà un array di ID dei post. Ho fatto questa modifica per dare senso all'uso di $wpdb. Se vuoi recuperare tutti i campi usa SELECT * invece di SELECT DISTINCT $wpdb->posts.ID, usa $wpdb->get_results invece di $wpdb->get_col e aggiungi la riga GROUP BY $wpdb->posts.ID alla query.

Due note:

Ricerca nei metadati

La regola è semplice: i metadati serializzati (come un array convertito in a:1:{i:0;s:2:"10";}) non sono pensati per essere ricercabili. Dovrai convertire il tuo dataset in valori singoli, così potrai effettuare ricerche corrette con meta_query.

L'unico modo veramente funzionante per effettuare questa ricerca è interrogare tutti i dati, poi deserializzarli, renderizzarli se corrispondono ai tuoi criteri o saltarli. Ci sono abbastanza discussioni e domande/risposte su SO che coprono questo argomento.

Prepared statements

C'è like_escape(), che deve essere usato così:

"%".like_escape( $string )."%"

Il motivo per cui devi aggiungere i caratteri % all'inizio e alla fine è semplice: puoi decidere tu stesso se il LIKE deve avvenire su entrambi i lati o solo su uno (inizio, fine, entrambi).

Le virgolette servono per assicurarsi che 10 non corrisponda anche a 100 ecc.

Funziona quando incollo il valore esatto del meta, cioè: a:1:{i:0;s:2:"10";}

Questo perché nella tua query WHERE, hai usato = dove il valore contiene %. Usando LIKE invece risolveresti questo problema specifico. Cambia meta_value = %s in meta_value LIKE %s, e la tua query dovrebbe funzionare come se avessi usato la stringa serializzata completa.

restituisce anche tutte le revisioni, non solo il post più recente.

Assicurati di specificare il tipo di post e lo stato corretti quando esegui la query. Se vuoi includere tutti i post che non sono revisioni, dovrai unire la tabella dei post (prima della tua clausola WHERE).

LEFT JOIN $wpdb->posts ON $wpdb->posts.ID = $wpdb->postmeta.post_id

e includi la seguente riga nella tua clausola WHERE (collegata da un AND).

$wpdb->posts.post_type NOT IN ('revision')