MySQLの GROUP CONCAT で記事一覧にタグの情報をつけて出力してみる

2018年8月22日ブログ運営

おはようございます。

以前、WordPressの記事一覧を抽出する方法を記事にしましたが、
今回はそれにタグの情報も加えてみたいと思います。

前回の記事も参考にしてください。

wpXクラウドで運営中ブログの記事一覧をMySQLからエクスポートする | ドラブロ –

wpXクラウドを利用していて、今まで書いてきたブログ記事を分析するために記事一覧が欲しい場合に、MyS ...

https://www.doraxdora.com/blog/2018/06/07/post-4846/

1. GROUP CONCAT 関数
2. データのエクスポート
- 2.1. タグを出力するクエリ
- 2.2. 記事一覧にタグをつけて出力するクエリ
3. まとめ

GROUP CONCAT 関数

GROUP BY でデータを集約する際に、
指定したカラムを（区切り文字で）連結、文字列データとして出力できる関数。

例えば次のようなテーブルを

SAMPLEテーブル

NO	所属	従業員
1	部門1	社員1
2	部門1	社員2
3	部門1	社員3
4	部門2	社員4
5	部門2	社員5

所属でグルーピングして従業員を連結することができます。

SELECT 所属 GROUP_CONCAT(従業員) FROM SAMPLE

1	SELECT所属GROUP_CONCAT(従業員)FROMSAMPLE

抽出結果

所属	従業員
部門1	社員1,社員2,社員3
部門2	社員4,社員5

データのエクスポート

タグを出力するクエリ

select 
        p.id
        , group_concat(t.name order by t.name) as tag
from
        wp_terms t 
        left outer join wp_term_taxonomy x on (
                t.term_id = x.term_id
        )
        left outer join wp_term_relationships r on (
                x.term_taxonomy_id = r.term_taxonomy_id
        )
        left outer join wp_posts p on (
                r.object_id = p.id
        )
where
        x.taxonomy = 'post_tag'
group by
        p.id, p.post_title

select

p.id

,group_concat(t.nameorder byt.name)astag

from

wp_termst

left outer joinwp_term_taxonomyxon(

t.term_id=x.term_id

)

left outer joinwp_term_relationshipsron(

x.term_taxonomy_id=r.term_taxonomy_id

)

left outer joinwp_postspon(

r.object_id=p.id

)

where

x.taxonomy='post_tag'

groupby

p.id,p.post_title

抽出結果

記事一覧にタグをつけて出力するクエリ

    select
        p.id
        , REPLACE(p.post_date, "-", "/") as post_date
        , REPLACE(p.post_modified, "-","/") as post_modified
        , concat('ルートURL', date_format(p.post_date, '%Y'), '/', date_format(p.post_date, '%m'), '/', date_format(p.post_date, '%d'), '/', p.post_name, '/') as post_name
        , p.post_title
        , IFNULL(t.tag, '') as tag
    from
        wp_posts p left outer join (
                    select 
                            p.id
                            , GROUP_CONCAT(t.name order by t.name) as tag
                    from
                            wp_terms t 
                            left outer join wp_term_taxonomy x on (
                                    t.term_id = x.term_id
                            )
                            left outer join wp_term_relationships r on (
                                    x.term_taxonomy_id = r.term_taxonomy_id
                            )
                            left outer join wp_posts p on (
                                    r.object_id = p.id
                            )
                    where
                            x.taxonomy = 'post_tag'
                    group by
                            p.id, p.post_title
        ) t on (
                p.id = t.id
        )
    where
        p.post_status = 'publish'
        and p.post_type in ('post', 'page')
    order by
        p.post_date

select

p.id

,REPLACE(p.post_date,"-","/")aspost_date

,REPLACE(p.post_modified,"-","/")aspost_modified

,concat('ルートURL',date_format(p.post_date,'%Y'),'/',date_format(p.post_date,'%m'),'/',date_format(p.post_date,'%d'),'/',p.post_name,'/')aspost_name

,p.post_title

,IFNULL(t.tag,'')astag

from

wp_postspleft outer join(

select

p.id

,GROUP_CONCAT(t.nameorder byt.name)astag

from

wp_termst

left outer joinwp_term_taxonomyxon(

t.term_id=x.term_id

)

left outer joinwp_term_relationshipsron(

x.term_taxonomy_id=r.term_taxonomy_id

)

left outer joinwp_postspon(

r.object_id=p.id

)

where

x.taxonomy='post_tag'

groupby

p.id,p.post_title

)ton(

p.id=t.id

)

where

p.post_status='publish'

andp.post_typein('post','page')

order by

p.post_date

抽出結果

まとめ

GROUP CONCAT関数の便利さに驚愕しますね。

この関数が無い場合はもっと複雑なクエリを書かないといけないか、
もうそれは諦めて一旦抽出したデータを Excel 等で加工するしかありませんし。

カテゴリーについても同様に取得できますし、WordPressのテーブル構造なんかについては
色々なサイトで紹介されていますので調べてみてください。
（いつかまとめるかも）

何かの参考になれば。

ではでは。

2018年8月22日ブログ運営GROUP_CONCAT,MySQL,wpX,wpXクラウド,エクスポート,ブログ,レポート,記事一覧

Posted by doradora