2016年12月28日水曜日

【備忘録】WordPress で特定カテゴリーに限定した月別アーカイブの出し方

特定カテゴリの月別アーカイブを表示したいです（WordPerss.org フォーラム）に触発されて、久々にSQL文にチャレンジしたと思っていろいろやっていたら出来たようなので、メモっておきます。なお考えたのはデータの取得までで、表示は上記フォーラムのものをそのまま使っています。

最初にコード全体を見せて、次にそれぞれの説明をしていきます。

参考情報




コード


青文字が考えた部分です。それ以外は変更していません。

<?php // カテゴリーなし（slug名）のみの月別アーカイブ表示

$year_prev = null;
$slug = 'カテゴリーなし';

 // 日本語のエンコード処理
$slug = urlencode($slug);
// エンコードした場合、%が入る。SQLでは %は %% とエスケープが必要
if(preg_match("/%/", $slug)):
  $slug = str_replace('%','%%', $slug);
endif;

 // SQLクエリ
$query = $wpdb->prepare("SELECT DISTINCT MONTH( post_date ) AS month ,
YEAR( post_date ) AS year,
COUNT( id ) as post_count FROM $wpdb->posts
WHERE post_status = 'publish' and post_date <= now( )
and post_type = 'post'
and id IN(
SELECT object_id FROM wp_term_relationships
WHERE term_taxonomy_id = (
SELECT term_taxonomy_id FROM wp_term_taxonomy AS tt
INNER JOIN wp_terms AS tm ON tt.term_id = tm.term_id
WHERE tm.slug = '%s'
) 
)
GROUP BY month , year
ORDER BY post_date DESC",$slug);
$months = $wpdb->get_results($query);

foreach($months as $month) :
$year_current = $month->year;
if ($year_current != $year_prev){
if ($year_prev != null){?>
</ul>
<?php } ?>
<h3><?php echo $month->year; ?></h3>
<ul class="archive-list">
<?php } ?>
<li>
        <a href="<?php bloginfo('url') ?>/<?php echo $month->year; ?>/<?php echo date("m", mktime(0, 0, 0, $month->month, 1, $month->year)) ?>"> <span class="archive-month"><?php echo date("n月", mktime(0, 0, 0, $month->month, 1, $month->year)) ?></span> </a>
</li>
<?php $year_prev = $year_current;
endforeach; ?>
</ul>

以下、青色部分について説明していきたいと思います。



1. エスケープとセキュリティ対策


スラッグのエンコードとエスケープ処理


$slug = urlencode('カテゴリーなし');

 // 日本語のエンコード処理
$slug = urlencode($slug);
// エンコードした場合、%が入る。SQLでは %は %% とエスケープが必要
if(preg_match("/%/", $slug)):
  $slug = str_replace('%','%%', $slug);
endif;

 アルファベットや記号などなら問題ないのですが、日本語などの場合に WordPress では urlencode された値が保存されます。それは % が分だんに入ることになり、SQLクエリでは % は %% とエスケープが必要のため、その措置を入れています。

SQLインジェクション対策


$wpdb->prepare("◯◯  %s", $slug);

SQLクエリに変数が含まれる場合には、SQLクエリのエスケープが必要です。esc_sqlもありますが、構文チェックもしてくれる $wpdb->prepare を使うのが推奨されてますね。最初にSQLクエリを入れて、そこに変数の値を含める場合には、%s が文字列、 %dが数字 を入れます。そして、あとから変数を書くというスタンスです。

2. 指定したカテゴリーを条件とするクエリ 


id IN(
SELECT object_id FROM wp_term_relationships
WHERE term_taxonomy_id = (
SELECT term_taxonomy_id FROM wp_term_taxonomy AS tt
INNER JOIN wp_terms AS tm ON tt.term_id = tm.term_id
WHERE tm.slug = '%s'
) 
)

 です。 %s は スラッグ名をいれることになります。
手順としては次の通りです。

  1. wp_terms の slug 情報を元に、wp_term_taxonomy テーブルより指定したカテゴリーに紐づくタクソノミーIDをゲットする
  2. 指定したカテゴリーに紐づくタクソノミIDを元に、wp_term_relationships テーブルより、投稿データの id 情報（object_id）をゲットする
  3. 投稿データの id 情報（object_id）を元に、wp_posts より該当する id を条件とする式を完成させる

複雑そうに見えますが、順を追ってみてみましょう。

1. wp_terms の slug 情報を元に、wp_term_taxonomy テーブルより指定したカテゴリーに紐づくタクソノミーIDをゲットする


SELECT term_taxonomy_id FROM wp_term_taxonomy AS tt
INNER JOIN wp_terms AS tm ON tt.term_id = tm.term_id
        WHERE tm.slug = '%s'

 ASは別名ということになるのですが、それを使わなければ

 SELECT term_taxonomy_id FROM wp_term_taxonomy 
INNER JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
        WHERE  wp_terms.slug  = '%s'

ですね。wp_term_taxonomy テーブルの term_id と wp_terms テーブルの term_id をベースに、wp_term_taxonomy テーブルとtermsテーブルを結合させます。その上で、wp_terms テーブルの slug 内から 指定したスラッグ名を抽出し、ヒットしたデータについて結合したテーブルから term_taxonomy_id を抽出します。
ここで何故結合が必要かというと、スラッグ情報が入っている wp_terms には term_taxonomy_id は存在しないため、２つのテーブルを結合させた上で、必要な情報をゲットするためです。

2. 指定したカテゴリーに紐づくタクソノミIDを元に、wp_term_relationships テーブルより、投稿データの id 情報（object_id）をゲットする


SELECT object_id FROM wp_term_relationships
       WHERE term_taxonomy_id = (
            SELECT term_taxonomy_id FROM wp_term_taxonomy AS tt
        INNER JOIN wp_terms AS tm ON tt.term_id = tm.term_id
                WHERE tm.slug = '%s'
       )

タクソノミー情報（term_taxonomy_id）を元に、wp_term_relationships テーブルから、object_idをゲットする。青文字のところだけですが、これはまぁ分かりやすいかなと思います。

3. 投稿データの id 情報（object_id）を元に、wp_posts より該当する id を条件とする式を完成させる


id IN(
SELECT object_id FROM wp_term_relationships
WHERE term_taxonomy_id = (
SELECT term_taxonomy_id FROM wp_term_taxonomy AS tt
INNER JOIN wp_terms AS tm ON tt.term_id = tm.term_id
WHERE tm.slug = '%s'
)
)

ゲットした object_id は複数存在する可能性があります。
そのため id = () ではダメで、id IN () を利用する必要があります。それらが含まれていた場合ってことですね。これを WHERE の条件にいれて wp_posts テーブルからデータをゲットするという流れですね。

このSQLクエリが最善かは分かりませんが、カテゴリー情報と投稿（固定ページ含む）データがどのように結びついているか勉強になりますね。実際に phpmyadminツールを利用してデータベースのテーブルデータを見ながら、また参考情報を参考にしながら作ってみました。

2016年12月28日 @kimipooh

投稿者 時刻:
ラベル:

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)