投稿一覧を取得するSQL【WordPress】

SELECT 
  p.post_title as 'タイトル',
  # ここの「example.com」↓はそれぞれのサイトに合わせて下さい
  CONCAT('https://example.com/',  p.post_name, '/') AS 'URL',
  c.category_name as 'カテゴリ名',
  IFNULL(t.tag_name, '') AS 'タグ名',
  p.post_date as '初回投稿日時',
  p.post_modified as '更新日時'
FROM wp_posts p
LEFT JOIN wp_users u
  ON p.post_author = u.ID
LEFT JOIN (
  SELECT cat_a.object_id, GROUP_CONCAT(cat_b.name) AS 'category_name'
  FROM wp_term_relationships cat_a, wp_terms cat_b, wp_term_taxonomy cat_c
  WHERE cat_a.term_taxonomy_id = cat_b.term_id
    AND cat_a.term_taxonomy_id = cat_c.term_id
    AND cat_c.taxonomy = 'category'
  GROUP BY cat_a.object_id
) c
  ON p.ID = c.object_id
LEFT JOIN (
  SELECT tag_a.object_id, GROUP_CONCAT(tag_b.name) AS 'tag_name'
  FROM wp_term_relationships tag_a, wp_terms tag_b, wp_term_taxonomy tag_c
  WHERE tag_a.term_taxonomy_id = tag_b.term_id
    AND tag_a.term_taxonomy_id = tag_c.term_id
    AND tag_c.taxonomy = 'post_tag'
  GROUP BY tag_a.object_id
) t
  ON p.ID = t.object_id
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'
ORDER BY p.ID ASC