【WordPress】よく読まれている記事はどれか調べる【SQL】

私はWordPressでブログを書いています。WordPressのダッシュボードの投稿記事一覧の中に、「閲覧数」と言う項目があります。

これはいつだったか失念しましたが、私がSEO対策で何かのプラグインを入れたときに、functions.phpにアクセス数をカウントする関数が追加されたからのようです。

この値はSQLのどのテーブルのどの項目に値が格納されているのだろうと興味を持ちましたので、調べてみました。

最終的には、よく読まれている記事TOP10をウィジェットに表示できたらよいな！という気持ちがあります。

閲覧数はメタデータの中にある

結論から言うと、メタデータの中にありました。

wp_postmetaというテーブルです。(WordPressのデータベースを見る方法は、以前の記事を見てください)

meta_keyにはいろいろな値が入っていまして、その中に”post_views_count”という値が入っていたら、meta_valueの値がカウントアップされるという仕様のようでした。

SELECT * FROM `wp_postmeta` WHERE `meta_key`='post_views_count';

テーブルレコード数を見たら、今のところ6000件程度ですが、今後記事が増えてきたらかなり大きなテーブルとなるような気がします。

WordPressが重たくなる原因が分かった気がします・・・。

meta_valueはやっかい

やっかいなのが、meta_valueです。longtext型なので、普通にORDER BY(並び替え)しても、次のように文字コード順となり、意図した通りにはデータが得られません。

SELECT * FROM `wp_postmeta` 
WHERE `meta_key`='post_views_count'
ORDER BY `meta_value` DESC;

数値に変換して並び替え

数値に変換すればよいと思い、CONVERT関数を試したところ、エラーとなりました。なんで？

SELECT * FROM `wp_postmeta` 
WHERE `meta_key`='post_views_count'
ORDER BY CONVERT(int,`meta_value`) DESC;

原因は、CONVERT関数の使い方でした。phpMyAdmin(MariaDB)の世界では、CONVERTは先にカラム名が来るそうです。SQLServerとは違いますね・・・

× CONVERT(型, カラム名)

○ CONVERT(カラム名, 型)

MariaDBはMySQLの系統らしいです。

正しく直したら、今度は正しく並べ替えができました。

先頭5件を抽出するには

では、上位5件だけを取り出すにはどうしたらよいのでしょう？

昔の私のうろ覚え記憶では、中間テーブルを作って、そのときROW_NUMBER()を付与しておいて、最終的に取り出すときに、ROW_NUMBER()<=5としていましたが・・・・・PHPMyAdminでは、もっと簡単でした。

LIMIT 0,5をつければよいようです。

SELECT * FROM `wp_postmeta` 
WHERE `meta_key`='post_views_count'
ORDER BY CONVERT(`meta_value`,int) DESC
LIMIT 0,5;

ここまで出来たら、あとはpost_idをキーに、投稿記事のURLとタイトルをゲットすれば、ブログに表示できそうですね。

長くなりそうなので、本日はここまで。