SQL初心者がcronで特定のカスタムフィールドの値が登録されたエントリーの件数を数えるまで
目次
昨年の年末にカスタムフィールドによる検索で、検索ボタンにそのフィールドによる検索対象のエントリーが何件あるかを表示したいと行った依頼がありました。カテゴリーによる絞り込みであれば、Category_List モジュールに {amount} という変数があるので対応できるのですが、カスタムフィールドを表示する変数にはなさそうなので、標準では対応できなさそうです…。
それなら独自でSQLを書いて実装してみようということでチャレンジしてみました。また、一道さんから 件数を登録する用のカスタムフィールドをつくって、件数を数えて登録するところは cron で1日1回実行するようにしようという指示を頂きましたので、cron の実装も行うことになりました。
最初は SQL?? 難しそう… a-blog cms の拡張もPHPを書くことになりそうだし、拡張のお作法とかわからないし、それに加えて cron ってなにそれおいしいの？ という感じでしたがなんとか実装することができました。
こんかいのセッションでは、SQL初心者がcronで特定のカスタムフィールドの値が登録されたエントリーの件数を数えるまでの道筋を順を追ってご紹介できたらと思います。
最終成果物
実装後の最終成果物としては ↓ の画像のようになります。左の営業所についてはカテゴリーですが、右の職種についてはカスタムフィールドです。通常ではカスタムフィールドでは件数を表示することができないのですが、この画像では件数を表示できていることがわかると思います。
公式ドキュメントを読む
まずは、a-blog cms を拡張するためのお作法を勉強するため以下の公式ドキュメントを読み込みました。
cron について調べる
まずはみんな大好きwikipedia から調べる
他にもGoogle で 「cron」と調べると1番目と2番目に出てくる以下の記事も助かりました。
環境
サーバー：XSERVER
a-blog cms : Ver. 2.11.41
PHP : ver. 7.4.25
実装
まずはcronでプログラムを実行できるか試してみたいと思います。Ver. 2.11 以降 の a-blog cms には /cron/example.php が cron でなにかしたいとき用のファイルとして標準装備されているので、このファイルを cron で実行してみました。
// cron/example.php
<?php
// 設置場所に合わせて、php/standalone のパスを合わせてください。
require_once dirname(__FILE__) . '/../php/standalone.php';
set_time_limit(0);
ini_set('memory_limit', '512M');
acmsStandAloneRun(function () {
acmsStdMessage('[Start] 処理を開始しました');
try {
/**
* ここに処理を追加
*
* 例: キャッシュを削除する場合
* ACMS_POST_Cache::clear('self-or-descendant');
* ACMS_POST_Cache::refresh('self-or-descendant');
*/
acmsStdMessage('[Success] 処理を完了しました');
} catch (\Exception $e) {
acmsStdMessage('[Error] ' . $e->getMessage());
return false;
}
return true;
});XSERVER のコントロールパネルからの設定
ターミナルからの設定
ssh で XSERVER に接続してから以下のコマンドを実行すると、vim が立ち上がり、cronの設定ファイルを記述できるようになります。
crontab -e設定ファイルには以下のように記述します。
45 15 8 3 * php /home/path/to/cron/example.php意味としては、3月8日の15時45分に cron/example.php を実行するよという意味です。
しかしここで問題発生。エラー文は忘れてしまったのですが、ionCube Loader がなんちゃらかんちゃら というエラーが出てしまい、cron/example.php が正常に動作しません。
もしかしたらCLI版のPHPコマンドで実行していて、php.ini の設定値が反映されておらず、エラーになってたのかもしれません。
XSERVERでは PHP のどのバージョンを使用するか、CLI版・CGI版どちらのPHPを使用するかをコマンドによって選択できるみたいなので、CGI版のPHPコマンドで cron/example.php を実行してればよかったのかも。
XSERVERのプログラム言語・コマンドパス
そこで、cron で実行する処理としては、HTTP通信でPOSTリクエストを送信して、POSTモジュールを実行するだけにして、特定のカスタムフィールドがエントリーを数えて、データベースの更新をするのは専用のPOSTモジュールを作成して実装することにします。
そのため、cron/example.php の中身を以下のようにします。
<?php
if (!is_file(dirname(__FILE__) . '/../config.server.php')) {
die('config.server.php is missing');
}
require_once dirname(__FILE__) . '/../config.server.php';
if (SSL_ENABLE && FULLTIME_SSL_ENABLE && COOKIE_SECURE ) {
$protocol = 'https';
} else {
$protocol = 'http';
}
$url = $protocol . '://' . DOMAIN . '/';
$postdata = http_build_query(
array(
'ACMS_POST_CountOccupation' => 'true',
'formToken' => 'hoge',
'password' => 'xxxxxxxxxx'
)
);
$opts = array('http' =>
array(
'method' => 'POST',
'header' => 'Content-type: application/x-www-form-urlencoded',
'content' => $postdata
)
);
$context = stream_context_create($opts);
$result = file_get_contents($url, false, $context);↑ このようにプログラムを書くことで、HTML以外からでもモジュールを起動できます。記法は異なりますが、JavaScriptからでも同じことができます。
ここでのポイントはPOST時に password という key で適当な文字列を送信しているところです。POSTのHTTPリクエストを送信したら誰でも POST モジュールを実行できてしまうのはセキュリティ的によろしくないということで、予めパスワードとして任意の文字列を設定しておいて、POST時に password という key で送信された文字列を POSTモジュール側で取得して、その文字列が予めパスワードとして設定した文字列と異なる場合は処理を止めるように実装しました。
更にセキュリティを強化
これでは、毎回決められたパスワードになるので全ての文字列の組み合わせを試すロボットなどに攻撃されたら突破されてしまう可能性がある他、万が一文字列を窃取された場合にも突破されてしまいます。
実装難易度は少し高くなりますが、以下のような実装をすると更に強固なセキュリティ対策ができます。
① cron でランダムな文字列を生成し、ファイルに出力して保存
② HTTPリクエストで ① の文字列を password として送信
③ POSTモジュール側で ① で生成したファイルを読み込み、そのファイル内にある文字列と、送信されてきた文字列を比較して、同じであるかつファイルの生成時間が⑤分以内であれば処理をする
次に、POSTモジュールの実装をします。/extension/acms/POST/CountOccupation.php を作成して実装します。
<?php
namespace Acms\Custom\POST;
use ACMS_POST;
use DB;
use SQL;
class CountOccupation extends ACMS_POST
{
protected $isCSRF = false;
/**
* ターゲットBID
*
* @var int
*/
protected $bid = 3;
function post()
{
// セキュリティ対応
$password = $this->Post->get('password');
if ($password !== 'xxxxxxxxxx') {
die('password is not correct.');
}
$amountData = $this->getAmountData();
$BlogField = loadBlogField($this->bid);
$oids = $BlogField->getArray('oid');
foreach ($oids as $i => $oid) {
$sortNum = $i + 1;
$keyIndex = array_search($oid, array_column($amountData, 'field_value'));
if ($keyIndex !== false) {
$this->updateAmount($amountData[$keyIndex]['amount'], $sortNum);
} else {
$this->updateAmount(0, $sortNum);
}
}
return $this->Post;
}
private function getAmountData()
{
$DB = DB::singleton(dsn());
$SQL = SQL::newSelect('field');
$SQL->addSelect('field_value');
$SQL->addSelect('field_value', 'amount', null, 'COUNT');
$where = SQL::newWhere();
$where->addWhereOpr('field_key', 'job_title');
$where->addWhereOpr('entry_status', 'open', '=', 'AND');
$where->addWhereOpr('entry_indexing', 'on', '=', 'AND');
$where->addWhereOpr('field_blog_id', $this->bid, '=', 'AND');
$SQL->addInnerJoin('entry', 'field_eid', 'entry_id', null, null, $where);
$SQL->addGroup('field_value');
$q = $SQL->get(dsn());
return $DB->query($q, 'all');
}
private function updateAmount($value, $sort)
{
$DB = DB::singleton(dsn());
$SQL = SQL::newUpdate('field');
$SQL->addWhereOpr('field_key', 'amount', '=');
$SQL->addWhereOpr('field_bid', $this->bid, '=', 'AND');
$SQL->addWhereOpr('field_sort', $sort, '=', 'AND');
$SQL->addUpdate('field_value', $value);
DB::query($SQL->get(dsn()), 'exec');
}
}実装のポイントがいくつかあるので、解説します
CSRF 対策をOFFにする
a-blog cms では CSRF対策としてPOSTモジュール実行時にa-blog cmsがフォームを表示するときに設定したトークンと呼ばれる特定の文字列と、POST時に自動的に送信されるトークンが一致しているかを確認してからPOSTモジュールの実行を行っています。そのため、基本的にPOSTモジュールを実行させたい場合には formToken を送信する必要があります。
テンプレート上から、POSTモジュールを実行する場合は form タグの最後に hidden で自動生成されている input タグの値が formToken として送信されます。
<input type="hidden" name="formToken" value="0f4fea9ab2718b9a214041f61c40dc7f12db750477e410a34cc833bd4aff847d">しかし今回は、PHP で HTTPリクエストを送信するため、formToken の値がありません。どうすればよいのでしょうか？
答えは簡単です。独自POSTモジュールのインスタンス変数に以下を追加することで、トークンが一致しているかどうかという判定を行わないようになります。
protected $isCSRF = false;ただ、トークンが存在しているかどうかのチェックは行われているので、空文字や適当な文字列を設定しておきます。
ただ、トークンが存在しているかどうかのチェックは行われているので、空文字や適当な文字列を設定しておきます。件数のデータを取得する
さて、今回の目的はとあるエントリーのカスタムフィールドの値が登録されたエントリーの件数を値ごとに数えて、件数を表示するカスタムフィールドの値を更新することでした。そのためにまずはエントリーのカスタムフィールドの値ごとに件数のデータを取得する必要があります。下記のような多次元配列でデータを取得します。
[
[0]=> [
[0]=> "field_valueの値",
[1]=> "件数の値",
]
[1]=> [
[0]=> "field_valueの値",
[1]=> "件数の値",
]
[2]=> [
[0]=> "field_valueの値",
[1]=> "件数の値",
]
]getAmountData メソッドの部分です。
private function getAmountData()
{
$DB = DB::singleton(dsn());
$SQL = SQL::newSelect('field');
$SQL->addSelect('field_value');
$SQL->addSelect('field_value', 'amount', null, 'COUNT');
$where = SQL::newWhere();
$where->addWhereOpr('field_key', 'job_title');
$where->addWhereOpr('entry_status', 'open', '=', 'AND');
$where->addWhereOpr('entry_indexing', 'on', '=', 'AND');
$where->addWhereOpr('field_blog_id', $this->bid, '=', 'AND');
$SQL->addInnerJoin('entry', 'field_eid', 'entry_id', null, null, $where);
$SQL->addGroup('field_value');
$q = $SQL->get(dsn());
return $DB->query($q, 'all');
}a-blog cms で用意されているDBクラスとSQLクラスを使ってSQLを組み立てていますが、実際のSQL文に直すと以下のようになります。
SELECT
field_value,
COUNT(field_value) AS amount
FROM
acms_field
INNER JOIN acms_entry
ON acms_fifield_eid=acms_entry.entry_id
WHERE acms_field.field_key = "job_title"
AND acms_entry.entry_status = "open"
AND acms_entry.entry_indexing = "on"
AND acms_field.field_blog_id = 3
GROUP BY field_value;データベースのクライアントツールである Seaquel Pro で実行してみると以下のように値とその値が登録されているエントリーが何件あるかというデータが取得できているのがわかります。
ポイントは 以下の2行になります。
$where->addWhereOpr('entry_status', 'open', '=', 'AND');
$where->addWhereOpr('entry_indexing', 'on', '=', 'AND');entry_status が open かつ entry_indexing が on のエントリーと言う条件を追加することで、公開されていて、一覧に並ぶようになっているエントリーのみを対象とすることができます。
件数を表示するカスタムフィールドの値を更新する
件数用のカスタムフィールドとしてブログのカスタムフィールドに amount というフィールドをカスタムフィールドグループで作成しました。このフィールドに件数のデータを挿入（正確には更新）していくことで Blog_Field などの通常のモジュールの中で {amount} と記述するだけで件数の表示ができるようにします。
<h2 class="acms-admin-admin-title2">職種の登録</h2>
<table class="js-fieldgroup-sortable adminTable acms-admin-table-admin-edit">
<thead class="acms-admin-hide-sp">
<tr>
<th class="acms-admin-table-left acms-admin-admin-config-table-item-handle"> </th>
<th class="acms-admin-table-left">職種名</th>
<th class="acms-admin-table-left">ID
<i class="acms-admin-icon-tooltip js-acms-tooltip js-acms-tooltip-hover" data-acms-tooltip="職種名に紐付けられるIDです。"></i>
</th>
<!-- ここから追加 -->
<th class="acms-admin-table-left">件数</th>
<!-- ここまで追加 -->
<th class="acms-admin-table-left acms-admin-admin-config-table-action">削除</th>
</tr>
</thead>
<tbody>
<!-- BEGIN group_occupation:loop -->
<tr class="sortable-item">
<td class="item-handle acms-admin-table-nowrap">
<i class="acms-admin-icon-sort"></i>
</td>
<td>
<input type="text" name="occupation[{i}]" value="{occupation}" class="acms-admin-form-width-medium" />
</td>
<td>
<input type="text" name="oid[]" value="{oid}" class="acms-admin-form-width-mini" readonly/>
</td>
<!-- ここから追加 -->
<td>
<input type="number" name="amount[]" value="{amount}" class="acms-admin-form-width-mini" />
</td>
<!-- ここまで追加 -->
<td class="acms-admin-table-nowrap">
<input type="button" class="item-delete acms-admin-btn-admin acms-admin-btn-admin-danger" value="削除" />
</td>
</tr>
<!-- END group_occupation:loop -->
<tr class="sortable-item item-template">
<td class="item-handle acms-admin-table-nowrap">
<i class="acms-admin-icon-sort"></i>
</td>
<td>
<input type="text" name="occupation[]" value="" class="acms-admin-form-width-medium" />
</td>
<td>
<input type="text" name="oid[]" value="" class="acms-admin-form-width-mini" />
</td>
<!-- ここから追加 -->
<td>
<input type="number" name="amount[]" value="" class="acms-admin-form-width-mini" />
</td>
<!-- ここまで追加 -->
<td class="acms-admin-table-nowrap">
<input type="button" class="item-delete acms-admin-btn-admin acms-admin-btn-admin-danger" value="削除" />
</td>
</tr>
</tbody>
<tfoot>
<tr>
<td colSpan="5">
<input type="button" class="item-insert acms-admin-btn-admin" value="追加" />
</td>
</tr>
</tfoot>
</table>
<input type="hidden" name="@group_occupation[]" value="occupation" />
<input type="hidden" name="field[]" value="occupation" />
<input type="hidden" name="@group_occupation[]" value="oid" />
<input type="hidden" name="field[]" value="oid" />
<!-- ここから追加 -->
<input type="hidden" name="@group_occupation[]" value="amount" />
<input type="hidden" name="field[]" value="amount" />
<!-- ここまで追加 -->
<input type="hidden" name="field[]" value="@group_occupation" />実際の管理画面の表示は ↓ の画像のようになります。件数のところが自動で更新されるカスタムフィールドになります。
データベースの更新の部分は `post` メソッド と `updateAmount` メソッドで行われています。ここでのポイントは `post` メソッドです。`ACMS_POST` クラスを継承して作成されたクラス内ので定義されている `post` メソッドは POST モジュール実行時に自動的に実行されるようになっていることです。
`post` メソッド と `updateAmount` メソッドについて詳しく見ていきます。
function post()
{
$amountData = $this->getAmountData();
$BlogField = loadBlogField($this->bid);
$oids = $BlogField->getArray('oid');
foreach ($oids as $i => $oid) {
$sortNum = $i + 1;
$keyIndex = array_search($oid, array_column($amountData, 'field_value'));
if ($keyIndex !== false) {
$this->updateAmount($amountData[$keyIndex]['amount'], $sortNum);
} else {
$this->updateAmount(0, $sortNum);
}
}
return $this->Post;
}まず、 post メソッドについてです。最初に先程説明した getAmountData メソッドで件数のデータを取得しています。
次に loadBlogField という a-blog cms 組み込みの関数でブログのカスタムフィールドグループの値である oid を配列で oids という変数に代入しています。
$BlogField = loadBlogField($this->bid);
$oids = $BlogField->getArray('oid');a-blog cms ではこういった便利な組み込み関数が用意されています。今回の実装では ブログのカスタムフィールドから配列のフィールドを取得するために loadBlogField と getArray を使用していますが、 loadEntryField や loadCategoryField でエントリーやカテゴリーのカスタムフィールドの値を取得することもできますし。 getArray ではなく、 get でカスタムフィールドの値を取得することもできます。
ただ、1点注意なのが、これらの関数は内部的にSQLを発行していますので、便利だからといってむやみに使いすぎると処理が重くなってしまいます。
そして次ですが、先程取得した oids という配列をループさせて、 $amountData 内の field_value が一致している配列があれば、その配列の amount の値で最初に用意したカスタムフィールドグループの amount の値を更新します。
また、field_value が一致している配列がなければ、件数が0件ということですので 0 でカスタムフィールドグループの amount の値を更新します。これはSQLのCOUNT関数で0件というのは取得できないためです。
カスタムフィールドグループのデータの更新でのポイントは acms_field テーブルの field_sort という値がカスタムフィールドグループの順番になっていることです。今回の例でも field_sort の値によって、フィールド名が同じカスタムフィールドグループの値でもそれぞれを識別することができます。
$SQL->addWhereOpr('field_sort', $sort, '=', 'AND');以上で独自POSTモジュールの実装は終わりです。上記のPOSTモジュールを cron で1日に1回など定期実行すれば、ブログのカスタムフィールドの値として、カスタムフィールドが登録されているエントリーを値ごとに数えた件数を表示することができます。
ver. 3.0.0 では…
昨年末にリリースされた a-blog cms には新機能として「カスタムフィールドキャッシュ」というキャッシュの仕組みが追加されています。そのため独自でPHPを書いてカスタマイズしてカスタムフィールドのデータに変更を加えた場合、管理画面 > ダッシュボード からカスタムフィールドキャッシュのクリアをしないと、サイトに反映されない可能性があります。ですが、毎回手動でキャッシュを削除するのは面倒ですのでプログラム上からカスタムフィールドキャッシュを削除するようにしたいですよね。
a-blog cms ではそのためのメソッドが用意されているのでそちらを使用します。PHPファイルの先頭で use Cache; を宣言してから以下の構文でカスタムフィールドキャッシュをクリアできます。
Cache::flush('field'); // 'page' | 'template' | 'config' | 'field' | 'temp'これで今回の発表は以上になります。
まとめ
今回はとあるカスタムフィールドが登録されたエントリーを、カスタムフィールドの値ごとに件数を表示する方法を紹介しました。カスタムフィールドによる検索機能を実装する場面で値ごとに件数を表示したいといった使い方ができるのではないかと思いました。（実際に今回実装した用途もそういった用途でした。）また、反省として何千件・何万件と対象のエントリーがある場合はこういった cron で定期実行するという実装方法が良いのかもしれませんが、今回実装した案件では、エントリー数も限られていたため、うまく校正オプションの拡張などで対応したら新しくカスタムフィールドを作成しなくても良かったかもしれないなと思いました。