Existen diversos motivos para crear una consulta SQL y ejecutarla directamente en la base de datos de WordPress (Generar estadísticas personalizadas, construir un plugin propio, generar un WebService, llevar el resultado a Excel, etc).
En nuestro caso, generaremos una consulta SQL que cumpla con los siguientes requerimientos:
- Muestre el nombre del autor de la publicación (display_name).
- Muestre el título de los posts (post_title).
- Muestre el contenido del post (post_content).
- Mostrar la URL física del post ( GUID = El almacén de URLs no amigables).
- Mostrar los enlaces permanentes o Permalinks (post_name).
- Mostrar el conteo de visualizaciones de cada posts.
- Mostrar los nombres de las categorías.
- Mostrar la fecha de publicación de los posts (post_date).
Requisito previo para qué la consulta SQL funcione y muestre el conteo de visitantes de los posts de WordPress.
Por default, WordPress no tiene un contador de visitas, por ello tiene que estar instalado el plugin Post Views Counter.
El plugin Post Views Counter crea la tabla wp_post_views de la cual extraeremos los datos de los visitantes.
Consulta SQL para generar el conteo de las visualizaciones de WordPress
La consulta, conecta las tablas wp_posts y wp_users mediante un LEFT JOIN para obtener los datos del post y el autor que los creo.
Se genera una sub consulta entre wp_posts y wp_post_views para obtener los conteos generales de cada posts de WordPress.
Finalmente, tenemos una sub consulta (wp_terms, wp_term_taxonomy y wp_term_relationships) que se conecta con el wp_posts para obtener las categorías separadas por comas «,»
SELECT DISTINCT
wp_users.display_name,wp_posts.post_title, wp_posts.post_content, wp_posts.guid,wp_posts.post_name,
(SELECT wp_post_views.count FROM wp_post_views WHERE wp_post_views.type=4 AND wp_post_views.id = wp_posts.ID GROUP BY wp_post_views.id) Count,
(SELECT GROUP_CONCAT(wp_terms.name separator ‘, ‘)
FROM wp_terms
INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships wpr ON wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE taxonomy= ‘category’ and wp_posts.ID = wpr.object_id
) AS Categories,
wp_posts.post_date
FROM wp_posts
LEFT JOIN wp_users
ON wp_posts.post_author=wp_users.ID
WHERE post_type = ‘post’ AND post_status = ‘publish’
ORDER BY post_date DESC
Resultado de la consulta SQL
Como resultado final, muestra el siguiente resultado desde que podremos exportar y llevar a Excel para generar un cuadro de producción:
Subscríbete a nuestro canal de YouTube @PCNautas
