select
wp_posts.id
,wp_posts.date
,wp_posts.post_title
,wp_users.display_name as 'writer'
,wp_category.name as 'category'
,wp_sub_category.name as 'sub_category'
,COALESCE(wp_tag.name, '') as 'tag'
from
(
select
id
,DATE_FORMAT(post_date,'%Y/%m/%d') as date
,post_title
,post_author
from
wp_posts
where
post_type='post'
and
post_status='publish'
) as wp_posts
left join
wp_users
on wp_posts.post_author = wp_users.id
left join
(
select
wp_term_relationships.object_id
,wp_terms.name
from
wp_term_relationships
left join
wp_terms
on wp_term_relationships.term_taxonomy_id = wp_terms.term_id
left join
wp_term_taxonomy
on wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_id
where
wp_term_taxonomy.taxonomy = 'category'
and wp_term_taxonomy.parent = 0
) as wp_category
on wp_posts.id = wp_category.object_id
left join
(
select
wp_term_relationships.object_id
,wp_terms.name
from
wp_term_relationships
left join
wp_terms
on wp_term_relationships.term_taxonomy_id = wp_terms.term_id
left join
wp_term_taxonomy
on wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_id
where
wp_term_taxonomy.taxonomy = 'category'
and wp_term_taxonomy.parent != 0
) as wp_sub_category
on wp_posts.id = wp_sub_category.object_id
left join
(
select
wp_term_relationships.object_id
,GROUP_CONCAT(wp_terms.name SEPARATOR'|') as name
from
wp_term_relationships
left join
wp_terms
on wp_term_relationships.term_taxonomy_id = wp_terms.term_id
left join
wp_term_taxonomy
on wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_id
where
wp_term_taxonomy.taxonomy = 'post_tag'
group by
wp_term_relationships.object_id
) as wp_tag
on wp_posts.id = wp_tag.object_id
;
