Forma más eficiente de obtener entradas con postmeta

Recomendaría una consulta de pivote. Usando tu ejemplo:

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

Me encontré con un caso donde necesitaba recuperar rápidamente una gran cantidad de publicaciones junto con su información meta asociada. Necesitaba recuperar alrededor de 2000 publicaciones.

Primero probé usando la sugerencia de Otto - ejecutando WP_Query::query para todas las publicaciones, y luego iterando y ejecutando get_post_custom para cada publicación. Esto tomó, en promedio, unos 3 segundos en completarse.

Luego probé la consulta pivot de Ethan (aunque no me gustó tener que pedir manualmente cada meta_key que me interesaba). Todavía tuve que iterar a través de todas las publicaciones recuperadas para deserializar el meta_value. Esto tomó, en promedio, unos 1.3 segundos en completarse.

Finalmente probé usando la función GROUP_CONCAT, y obtuve el mejor resultado. Aquí está el código:

global $wpdb;
$wpdb->query('SET SESSION group_concat_max_len = 10000'); // necesario para obtener más de 1024 caracteres en las columnas GROUP_CONCAT
$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);

// procesa los productos para tener un miembro ->meta con los valores deserializados como se espera
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);

Esto tomó en promedio 0.7 segundos. Eso es aproximadamente un cuarto del tiempo de la solución WP get_post_custom() y la mitad de la solución de consulta pivot.

Quizás esto le sea útil a alguien.

Me encontré en una situación donde necesitaba realizar esta tarea para finalmente crear un documento CSV. Terminé trabajando directamente con MySQL para hacerlo. Mi código une las tablas de posts y meta para recuperar la información de precios de WooCommerce, la solución publicada anteriormente requería que usara alias de tabla en el SQL para que funcionara correctamente.

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

Sin embargo, ten en cuenta que WooCommerce creó más de 300K filas en mi tabla meta, por lo que era muy grande y, por lo tanto, muy lenta.

VERSIÓN SIN SQL:

Obtener todos los posts y todos sus valores meta (metas) sin usar SQL:

Digamos que tienes una lista de IDs de posts almacenados como un array de IDs, algo como:

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

Obtener todos los posts y todas las metas en 1 consulta no es posible sin usar al menos un poco de SQL, así que debemos hacer 2 consultas (solo 2):

1. Obtener todos los posts (usando WP_Query)

$request = new WP Query([
  'post__in' => $post_ids_list,
  'ignore_sticky_posts' => true, //si quieres ignorar los posts "stickies"
]);

(No olvides llamar a wp_reset_postdata(); si vas a hacer un "loop" después ;) )

2. Actualizar la caché de metas

//no te confundas aquí: "post" se refiere al tipo de contenido (post X user X ...), NO al tipo de post ;)
update_meta_cache('post', $post_ids_list);

Para obtener los datos meta simplemente usa el estándar get_post_meta() que, como @Otto señaló:
¡primero revisa la caché! :)

Nota: Si no necesitas otros datos de los posts (como título, contenido, ...) puedes hacer solo el paso 2. :-)

utilizando la solución de Trevor y modificándola para que funcione con SQL anidado. Esto no ha sido probado.

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

Esta pregunta fue formulada hace unos 12 años, pero me encontré con este problema recientemente cuando necesité buscar tipos de publicación con un criterio más complejo de lo que se puede hacer con una WP_Query básica. Utilicé el siguiente código para hacer algo muy similar.

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

Otras respuestas en esta pregunta intentan algo diferente a simplemente usar LEFT JOIN como hizo el autor originalmente. A través de algunas pruebas, parece ser la forma más rápida de obtener los datos.

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;

Esta consulta es muy similar a otra respuesta en esta publicación de Terry y Ethan y tardó 0.0228 segundos en completarse, mientras que la primera consulta tardó unos 0.0034 segundos en completarse, es decir, aproximadamente 6.7 veces más rápida.

Esto se realizó en una base de datos con solo 322 publicaciones, pero cuando se probó sin limitarla al tipo de publicación personalizada y consultando 5234 publicaciones, la consulta anterior obtuvo un tiempo de 0.2408s y la primera consulta obtuvo un tiempo de 0.0029s, es decir, aproximadamente 83 veces más rápida. No estoy seguro de cómo fue más rápida con 5234 publicaciones frente a 332 publicaciones (lo probé dos veces para asegurarme).

En resumen: Para cualquiera que busque la solución más rápida en 2024 con una configuración predeterminada de WordPress, simplemente usar la consulta LEFT JOIN anterior parece ser la solución más rápida.

También me encontré con el problema de los campos meta con múltiples valores. El problema está en el propio WordPress. Si revisas en wp-includes/meta.php, busca esta línea:

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

El problema está con la sentencia CAST. En una consulta para valores meta, la variable $meta_type está configurada como CHAR. No conozco los detalles de cómo afecta el CAST del valor a CHAR a la cadena serializada, pero para solucionarlo, puedes eliminar el CAST para que el SQL quede así:

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

Ahora bien, aunque esto funciona, estás modificando los archivos internos de WordPress, por lo que otras cosas podrían romperse, y no es una solución permanente, asumiendo que necesitarás actualizar WordPress.

La forma en que lo he solucionado es copiar el SQL generado por WordPress para la consulta meta que quiero y luego escribir algo de PHP para agregar declaraciones AND adicionales para los meta_values que estoy buscando, usando $wpdb->get_results($sql) para el resultado final. Es un poco improvisado, pero funciona.