Last active
September 21, 2020 01:52
-
-
Save ligiadf/051753c7bea481264e005a04f006cb86 to your computer and use it in GitHub Desktop.
WordPress - Listar posts publicados: ID, data de publicação, título, conteúdo, categorias, tags e contagem de comentários
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Baseado em https://stackoverflow.com/questions/13071035/sql-query-to-extract-all-wordpress-posts-with-categories | |
SELECT DISTINCT | |
ID, | |
post_date as "Publicado em", | |
post_title "Título", | |
post_content "Conteúdo", | |
(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 "Categorias", | |
(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= 'post_tag' and wp_posts.ID = wpr.object_id | |
) AS "Tags", | |
comment_count as "Total de comentários" | |
FROM wp_posts | |
WHERE post_type = 'post' | |
AND post_status = 'publish' | |
ORDER BY 2 desc |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment