Modo più efficiente per ottenere i post con i postmeta

Consiglierei una query pivot. Usando il tuo esempio:

SELECT  p.ID,   
        p.post_title, 
        MAX(CASE WHEN pm1.meta_key = 'first_field' then pm1.meta_value ELSE NULL END) as first_field,
        MAX(CASE WHEN pm1.meta_key = 'second_field' then pm1.meta_value ELSE NULL END) as second_field,
        MAX(CASE WHEN pm1.meta_key = 'third_field' then pm1.meta_value ELSE NULL END) as third_field,

 FROM    wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)                      
GROUP BY
   p.ID,p.post_title

Mi sono imbattuto in un caso in cui ho bisogno di recuperare rapidamente un gran numero di post con le relative meta informazioni. Devo recuperare circa O(2000) post.

Ho provato utilizzando il suggerimento di Otto - eseguendo WP_Query::query per tutti i post e poi ciclando e richiamando get_post_custom per ogni post. Questo ha richiesto, in media, circa 3 secondi per completarsi.

Ho poi provato la query pivot di Ethan (anche se non mi piaceva dover chiedere manualmente ogni meta_key di interesse). Ho comunque dovuto ciclare tutti i post recuperati per deserializzare il meta_value. Questo ha richiesto, in media, circa 1.3 secondi per completarsi.

Ho infine provato utilizzando la funzione GROUP_CONCAT, ottenendo il risultato migliore. Ecco il codice:

global $wpdb;
$wpdb->query('SET SESSION group_concat_max_len = 10000'); // necessario per ottenere più di 1024 caratteri nelle colonne GROUP_CONCAT sotto
$query = "
    SELECT p.*, 
    GROUP_CONCAT(pm.meta_key ORDER BY pm.meta_key DESC SEPARATOR '||') as meta_keys, 
    GROUP_CONCAT(pm.meta_value ORDER BY pm.meta_key DESC SEPARATOR '||') as meta_values 
    FROM $wpdb->posts p 
    LEFT JOIN $wpdb->postmeta pm on pm.post_id = p.ID 
    WHERE p.post_type = 'product' and p.post_status = 'publish' 
    GROUP BY p.ID
";

$products = $wpdb->get_results($query);

// elabora i prodotti per avere un membro ->meta con i valori deserializzati come previsto
function massage($a){
    $a->meta = array_combine(explode('||',$a->meta_keys),array_map('maybe_unserialize',explode('||',$a->meta_values)));
    unset($a->meta_keys);
    unset($a->meta_values);
    return $a;
}

$products = array_map('massage',$products);

Questo ha richiesto in media 0.7 secondi. Circa un quarto del tempo della soluzione WP get_post_custom() e circa la metà della soluzione con query pivot.

Spero possa essere utile a qualcuno.

Mi sono trovato in una situazione in cui avevo bisogno di svolgere questo compito per creare alla fine un documento CSV, alla fine ho lavorato direttamente con MySQL per farlo. Il mio codice unisce le tabelle dei post e dei meta per recuperare le informazioni sui prezzi di WooCommerce, la soluzione precedentemente pubblicata richiedeva che utilizzassi degli alias di tabella nella query SQL per funzionare correttamente.

SELECT p.ID, p.post_title, 
    MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price,
    MAX(CASE WHEN pm1.meta_key = '_regular_price' then pm1.meta_value ELSE NULL END) as regular_price,
    MAX(CASE WHEN pm1.meta_key = '_sale_price' then pm1.meta_value ELSE NULL END) as sale_price,
    MAX(CASE WHEN pm1.meta_key = '_sku' then pm1.meta_value ELSE NULL END) as sku
    FROM wp_posts p LEFT JOIN wp_postmeta pm1 ON ( pm1.post_id = p.ID)                 
    WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish'
    GROUP BY p.ID, p.post_title

Tuttavia, attenzione: WooCommerce ha creato più di 300.000 righe nella mia tabella meta, quindi era molto grande e di conseguenza molto lenta.

VERSIONE SENZA SQL:

Ottieni tutti i post e tutti i loro valori meta (metadati) senza usare SQL:

Supponiamo che tu abbia una lista di ID di post memorizzati come un array di ID, qualcosa come:

$post_ids_list = [584, 21, 1, 4, ...];

Ottenere tutti i post e tutti i metadati in una singola query non è possibile senza usare almeno un po' di SQL, quindi dobbiamo fare 2 query (sempre e solo 2):

1. Ottieni tutti i post (usando WP_Query)

$request = new WP Query([
  'post__in' => $post_ids_list,
  'ignore_sticky_posts' => true, //se vuoi ignorare i post "in evidenza"
]);

(Non dimenticare di chiamare wp_reset_postdata(); se stai per fare un "loop" dopo ;) )

2. Aggiorna la cache dei metadati

//non confonderti qui: "post" si riferisce al tipo di contenuto (post X utente X ...), NON al post type ;)
update_meta_cache('post', $post_ids_list);

Per ottenere i metadati usa semplicemente la funzione standard get_post_meta() che, come ha sottolineato @Otto:
cerca prima nella cache :)

Nota: Se non ti servono altri dati dei post (come titolo, contenuto, ...) puoi fare solo il punto 2. :-)

utilizzando la soluzione di Trevor e modificandola per funzionare con SQL annidato. Questo codice non è stato testato.

global $wpdb;
$query = "
    SELECT p.*, (select pm.* From $wpdb->postmeta AS pm WHERE pm.post_id = p.ID)
    FROM $wpdb->posts p 
    WHERE p.post_type = 'product' and p.post_status = 'publish' 
";
$products = $wpdb->get_results($query);

questa domanda è stata posta circa 12 anni fa, ma mi sono imbattuto in questo problema recentemente quando ho dovuto cercare tipi di post con criteri più complessi di quelli gestibili con una semplice WP_Query. Ho utilizzato il codice qui sotto per fare qualcosa di molto simile.

SELECT p.ID,p.post_title,pm1.meta_value as lat,pm2.meta_value as lng,pm3.meta_value as city,pm4.meta_value as state,pm5.meta_value as zip
FROM `wp_posts` p
LEFT JOIN `wp_postmeta` pm1 ON pm1.post_id = p.ID AND pm1.meta_key = '_lat'
LEFT JOIN `wp_postmeta` pm2 ON pm2.post_id = p.ID AND pm2.meta_key = '_lng'
LEFT JOIN `wp_postmeta` pm3 ON pm3.post_id = p.ID AND pm3.meta_key = '_city'
LEFT JOIN `wp_postmeta` pm4 ON pm4.post_id = p.ID AND pm4.meta_key = '_state'
LEFT JOIN `wp_postmeta` pm5 ON pm5.post_id = p.ID AND pm5.meta_key = '_zip'
WHERE post_type = "{posttype}" and post_status = "publish";

Altre risposte a questa domanda provano approcci diversi dall'uso del LEFT JOIN come fatto originariamente dall'autore. Dopo alcuni test, sembra effettivamente essere il modo più veloce per ottenere i dati.

SELECT  p.ID,   
p.post_title, 
MAX(CASE WHEN pm1.meta_key = '_lat' then pm1.meta_value ELSE NULL END) as lat,
MAX(CASE WHEN pm1.meta_key = '_lng' then pm1.meta_value ELSE NULL END) as lng,
MAX(CASE WHEN pm1.meta_key = '_city' then pm1.meta_value ELSE NULL END) as city,
MAX(CASE WHEN pm1.meta_key = '_state' then pm1.meta_value ELSE NULL END) as state,
MAX(CASE WHEN pm1.meta_key = '_zip' then pm1.meta_value ELSE NULL END) as zip
FROM    
    wp_posts p LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID WHERE p.post_type = '{posttype}' AND p.post_status = 'publish'                     
GROUP BY
   p.ID,p.post_title;

Questa query è molto simile a un'altra risposta in questo post di Terry ed Ethan e ha impiegato 0.0228 secondi per completarsi, mentre la prima query ha impiegato circa 0.0034 secondi, ovvero circa 6.7 volte più veloce.

Il test è stato eseguito su un database con solo 322 post, ma quando testato senza limitarlo al custom post type e interrogando 5234 post, la query sopra ha ottenuto un tempo di 0.2408s mentre la prima query ha ottenuto 0.0029s, ovvero circa 83 volte più veloce. Non sono sicuro del perché sia stata più veloce con 5234 post rispetto a 332 post (ho testato due volte per esserne certo).

TLDR: Per chiunque cerchi la soluzione più veloce nel 2024 con un setup WordPress standard, usare semplicemente la query LEFT JOIN sopra sembra essere la soluzione più performante.

Anch'io mi sono imbattuto nel problema dei campi meta con valori multipli. Il problema è nello stesso WordPress. Guarda in wp-includes/meta.php. Cerca questa riga:

$where[$k] = ' (' . $where[$k] . $wpdb->prepare( "CAST($alias.meta_value AS {$meta_type}) {$meta_compare} {$meta_compare_string})", $meta_value );

Il problema è con l'istruzione CAST. In una query per i valori meta, la variabile $meta_type è impostata su CHAR. Non conosco i dettagli su come il CAST del valore in CHAR influisca sulla stringa serializzata, ma per risolverlo, puoi rimuovere il cast così che l'SQL risulti così:

$where[$k] = ' (' . $where[$k] . $wpdb->prepare( "$alias.meta_value {$meta_compare} {$meta_compare_string})", $meta_value );

Ora, anche se funziona, stai modificando gli interni di WordPress, quindi altre cose potrebbero rompersi, e non è una soluzione permanente, supponendo che dovrai aggiornare WordPress.

Il modo in cui l'ho risolto è copiare l'SQL generato da WordPress per la meta query che desidero e poi scrivere del PHP per aggiungere ulteriori istruzioni AND per i meta_values che sto cercando e usare $wpdb->get_results($sql) per l'output finale. Un po' rozzo, ma funziona.