Custom DataBase Tablesでユーザー評価可能な製品管理ページを作る

Custom DataBase Tables（以降「CDBT」と呼ぶことにする）プラグインのバージョン2のチュートリアルを公開したのに合わせて、連携した記事を書き起こしてみた。原文のチュートリアルの方は技術者向けで表記が硬いので、もう少しライトな感じの記事も公開しておこうと思ったのだ。原文は長いので、こちらの記事ではインストール方法とかのくだりは省略して、ダイレクトに表題のお題を実践してみる。

新しいテーブルを作ろう

では早速、CDBTプラグインで新しいテーブルを作成していこう。WordPressの管理画面の「CDBT」メニューから「テーブル管理」ページの「テーブル作成」タブを開こう。

このページでは新しく作成したいテーブルの基本設定をすべて行うことができる。
この記事では設定項目別の詳しい説明は省略するので、詳しく知りたい場合はドキュメントのテーブル作成 ─ Create Tableを参照してみてほしい。

今回、簡易的な製品データベース用のテーブルと製品を評価するためのテーブルの二つを作成して、製品管理ページと製品評価ページをWordPressサイトで公開するところまでをやってみる。

まず、製品情報を格納する製品管理テーブルを作成していこう。テーブル名はwp_productsとする（テーブル名の欄に「wp_products」と入力しよう）。他の項目はそのままで、「テーブル作成SQL」欄の「テーブルクリエイター」をクリックして起動しよう。起動したら、下図を参照してテーブルを設計する。

テーブルクリエイターでの設計が終わったら「SQLを適用する」をクリックすると「テーブル作成SQL」欄に下記のSQLが生成される。テーブルクリエイターでの設計が面倒であれば、「テーブル作成SQL」欄の「SQL直接編集」のテキストエリアに下記のSQLをコピー＆ペーストしてもOKだ。

CREATE TABLE `wp_products` ( 
`ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID', 
`product_name` varchar(200) NOT NULL COMMENT 'Product Name', 
`supplier` varchar(200) COMMENT 'Supplier', 
`category` enum('undefined','category-a','category-b','category-c','category-d','category-e') NOT NULL COMMENT 'Category', 
`price` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Price', 
`image` mediumblob COMMENT 'Image', 
`reference_url` varchar(255) COMMENT 'Reference URL', 
`description` text COMMENT 'Description', 
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created Datetime', 
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated Datetime', 
PRIMARY KEY(`ID`)  ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;

続いて、作成するテーブルのプラグイン用の設定を行おう。今回のプロジェクトでは、製品管理テーブルへの製品登録やデータ編集はログインしていないユーザーにもアクセス権を与える建付けにするので、下記のようにデータ閲覧・データ登録・データ編集のすべてについてゲストユーザー以上にアクセス権を与えておこう。

これで製品管理テーブルの設定は完了だ。「テーブル作成」をクリックしよう。

次に、製品の評価データを収集する製品評価テーブルを作成する。テーブル名はwp_product reputeだ（テーブル名の欄に「wp_product_repute」と入力しよう）。今回のプロジェクトでは、評価の仕組みとして評価した日時等は管理しないことにするため、自動追加カラムはプライマリーキーのみにチェックして他はチェックを外そう。それ以外の設定値は特に変更しなくて構わない。

テーブルクリエイターを起動して、下記のようにテーブルを設計しよう。

設計後に生成されるSQLは下記の通りだ。テーブルクリエイターを使わず、下記のSQLをコピペしてテーブルを作っても大丈夫だ。

CREATE TABLE `wp_product_repute` ( 
`ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID', 
`product_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Product ID', 
`rater` varchar(100) COMMENT 'Rater', 
`score` tinyint(4) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'Score', 
`comment` text COMMENT 'Evaluation Comment', 
PRIMARY KEY(`ID`)  ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;

製品評価テーブルのユーザーのアクセス権も製品管理テーブルと同じ設定でOKだ。

最後に「テーブル作成」をクリックして、完了だ。
これで、製品管理テーブルと製品評価テーブルができた。次にこの二つのテーブルをリレーション（連携）させる。なぜかというと、製品評価テーブルのproduct_idカラムには製品管理テーブルのプライマリーキーであるIDカラムに存在する値しか指定できないようにするためだ。そのためには、wp_product_reputeテーブルのproduct_idカラムに外部キー制約を追加することになる。

テーブル管理の「テーブル編集」タブを開き、外部キー制約を追加するwp_product_reputeテーブルを選択して「編集テーブルの変更」をクリックしよう。

「テーブル本体スキーマの変更」セクションの「カスタムSQL（ALTER TABLE）」欄、「SQL直接編集」タブのテキストエリアに外部キー制約追加のSQLを入力する。

入力するSQLは下記の通りだ。これをコピペしてほしい。

ALTER TABLE wp_product_repute ADD FOREIGN KEY index_pid (product_id) REFERENCES wp_products (ID) ON DELETE CASCADE ON UPDATE CASCADE;

これで新規テーブルについての準備はすべて完了した。

カスタムショートコードを作ろう

新しく作成したテーブルをWEBページに出力するために、専用のショートコード（カスタムショートコード）を作成する。
CDBTプラグインで新しいショートコードを作成するには、WordPressの管理画面の「CDBT」メニューから「ショートコード管理」ページの「ショートコード登録」タブを開く。

上記のページで、新しく作成したいショートコードの各種設定を行っていくことになる。

今回のプロジェクトでは製品管理テーブルのデータを一覧表示するための[cdbt-view]のショートコードと、製品管理テーブルに製品データを登録するための[cdbt-entry]のショートコード、そして製品評価テーブルに評価を入力してもらうための[cdbt-entry]のショートコードの三つが必要になる。

まず一つ目のショートコード[cdbt-view]を作成しよう。「ショートコード登録」タブで「ベースショートコード名」にcdbt-viewを、「対象テーブル名」にwp_productsをそれぞれ選択する。すると、参考情報としてwp_productテーブルのカラム情報が表示されるはずだ。

「ショートコードの高度な設定」セクションで、下図の設定項目を参照して設定を行ってほしい。

設定が完了したら、「ショートコードを保存」ボタンをクリックしてカスタムショートコードとして保存する。カスタムショートコードのエイリアスIDが通知されるはずだ。

続いて二つ目のショートコード[cdbt-entry]を作成しよう。やり方は一つ目の時と同じで、「ベースショートコード名」にcdbt-entryを、「対象テーブル名」にwp_productsをそれぞれ選択する。そして、「ショートコードの高度な設定」セクションで下図の通り設定項目を指定してほしい。

ショートコードの作成は「ショートコードプレビュー」を利用して、現在の設定によるショートコード出力を確認しながら作成できるのだが、[cdbt-view]や[cdbt-edit]のショートコードはテーブル内にデータが存在しないと表示されないため、現時点ではプレビューが有効ではない。プレビューを利用する場合はあらかじめいくつかのデータを登録しておく必要があるのだ。

これで二つ目のショートコードの設定は完了だ。「ショートコードを保存」ボタンをクリックしてカスタムショートコードとして保存しよう。

最後に三つめのショートコード、製品評価テーブル用の[cdbt-entry]を作成しよう。やり方は二つ目の時と同様に、「ベースショートコード名」にcdbt-entryを、「対象テーブル名」にwp_product_reputeをそれぞれ選択すればOKだ。そして、「ショートコードの高度な設定」セクションでの設定項目は下図を見てほしい。

設定後、「ショートコードを保存」ボタンをクリックしてカスタムショートコードとして保存するのを忘れずに。

さて、これで必要な三つのショートコードが完成した。

ショートコードをページに設置しよう

作成したショートコードをWordPressサイトのページに設置していく。
まず、設置するショートコードの記述を確認しておく必要がある。そのためには「ショートコード管理」の「ショートコード一覧」タブを開こう。

一覧表の一番最後に作成した三つのショートコードが一覧表示されてはずだ。それらの「ショートコード名／エイリアスコード」列のエイリアスコードをすべて控えておこう（メモ帳あたりにコピペしておくと良いだろう）。

次にWordPressの新規投稿画面を開き、下図のような投稿を作成する。

投稿本文（テキスト）は下記の通りだ。

Currently registered product list:

[cdbt-view table="wp_products" csid="1"]

<hr />

Product Registration Form:

[cdbt-entry table="wp_products" csid="2"]

投稿が完成したら、さっそく「公開」して表示を確認してみよう。

この時点では、まだ製品管理テーブルにデータが登録されていないので、[cdbt-view]のショートコード部分が表示されないが、このページから製品データを登録できるようになっているはずだ。
では、実際にいくつかサンプルの製品データを登録してみよう。

それらしくなって来た…！
続いて、製品評価用のページを作ってみよう。再びWordPressの新規投稿画面を開いて、下図のような投稿を作成する。

投稿本文（テキスト）は下記の通りだ。

Currently registered product list:

[cdbt-view table="wp_products" csid="1"]

<hr />

Product Evaluation Form:

[cdbt-entry table="wp_product_repute" csid="3"]

これも「公開」して表示してみる。

これで製品管理ページと製品評価ページが完成だ。

しかし、このままでは製品データの閲覧や製品評価の入力についてはかなり使い勝手が悪い状態だ。例えば、この時点で製品評価フォームにProduct IDに99などの製品管理テーブルのIDカラムにない値を指定してデータ登録を行った場合、外部キー制約に違反して登録に失敗してしまう。しかも、製品についての現在の評価値が表示されていないので、評価してもそれを確認できないのだ。

ショートコードをカスタマイズしよう

最後の仕上げとして、出力されたショートコードの見た目や処理をカスタマイズして、プロジェクトを完成させよう。

データ一覧の見た目をカスタマイズしてみよう

[cdbt-view]や[cdbt-edit]で出力されるデータ一覧の表示をカスタマイズしていこう。
現時点のショートコードで出力される製品管理テーブルの表示構造は下記のようになっているはずだ。

Image	Product Name	Supplier	Category	Price	Description	Reference URL
画像ファイル	製品名	供給元	カテゴリー名	価格	説明	参照URL

この表示を下記のように変更してみよう。

Image	Product Name	Supplier	Category	Price	Description	Repute
画像ファイル	参照URLへのリンク付き製品名	供給元	カテゴリー名	単位「$」付き価格	ポップアップ形式の説明リンク	平均の評価スコア

さらに、製品のカテゴリーによって行の背景色も変更してみる。

[cdbt-view]や[cdbt-edit]で表示されるデータ一覧の表示をカスタマイズする時には、CDBTプラグインに用意されているcdbt_shortcode_custom_columnsフィルターとcdbt_shortcode_custom_component_optionsフィルターを使えば良い。それぞれのフィルター処理をテーマのfunctions.phpに追加していこう。

function custom_wp_products_crud_get_data_sql( $sql, $table_name, $sql_clauses ) {
  if ( in_array( get_post()->post_title, array( 'Product evaluation page', 'Product registration page' ) ) && 'wp_products' === $table_name ) {
    if ( strpos( $sql_clauses[0], 'image' ) !== false ) {
      // Join of table (outer join)
      $_overwrite_sql = <<<SQL
SELECT p.image,p.product_name,p.supplier,p.category,p.price,p.description,p.reference_url,AVG(r.score) as repute 
FROM wp_products p
LEFT OUTER JOIN wp_product_repute r ON p.ID=r.product_id 
GROUP BY p.ID 
ORDER BY p.product_name ASC 
SQL;
      $sql = $_overwrite_sql;
    }
  }
  return $sql;
}
add_filter( 'cdbt_crud_get_data_sql', 'custom_wp_products_crud_get_data_sql', 10, 3 );

まずcdbt_crud_get_data_sqlフィルターを使い、wp_productsテーブルに製品評価テーブルの各product_idごとのscoreカラムの平均値をreputeカラムとして外部結合（OUTER JOIN）する。これによって、製品管理ページのショートコード[cdbt-view]で新カラム（別名カラム）reputeを取り扱うことができるようになるのだ。

function custom_wp_products_cdbt_shortcode_custom_columns( $columns, $shortcode_name, $table ) {
  if ( in_array( get_post()->post_title, array( 'Product evaluation page', 'Product registration page' ) ) && in_array( $shortcode_name, array( 'cdbt-view', 'cdbt-edit' ) ) && 'wp_products' === $table ) {
    foreach ( $columns as $_i => $_column ) {
      $_custom_column = '';
      switch( $_column['property'] ) {
        case 'product_name':
          // Add a tag for link
          $_custom_column = 'rowData.reference_url != "" ? $("<a/>").attr("href",_.unescape(rowData.reference_url)).attr("target","_blank").text(_.unescape(rowData.product_name)) : rowData.product_name';
          break;
        case 'price':
          // Add string of unit as prefix
          $_custom_column = '"$ " + rowData.price';
          break;
        case 'description':
          // Add a popup link
          $_custom_column = 'rowData.description != "" ? $("<a/>").attr("href","javascript:;").attr("class","collapse-col-data btn btn-default btn-sm").data("raw",_.unescape(rowData.description)).text("More") : "-"';
          break;
        case 'reference_url':
          // Do not show
          unset( $columns[$_i] );
          break;
      }
      if ( ! empty( $_custom_column ) ) 
        $columns[$_i]['customColumnRenderer'] = $_custom_column;
    }
    // Add new column
    $columns = array_merge( $columns, array( 
      array(
        'label' => 'Repute', 
        'property' => 'repute', 
        'sortable' => true, 
        'sortDirection' => 'asc', 
        'dataNumric' => true, 
        'truncateStrings' => '0', 
        'className' => '', 
        'customColumnRenderer' => 'rowData.repute != null ? Number(rowData.repute) : "Unrated"'
      )
    ) );
  }

  return $columns;
}
add_filter( 'cdbt_shortcode_custom_columns', 'custom_wp_products_cdbt_shortcode_custom_columns', 10, 3 );

function custom_wp_products_cdbt_shortcode_custom_component_options( $component_options, $shortcode_name, $table ){
  if ( in_array( get_post()->post_title, array( 'Product evaluation page', 'Product registration page' ) ) && in_array( $shortcode_name, array( 'cdbt-view', 'cdbt-edit' ) ) && 'wp_products' === $table ) {
    $_custom_script = <<<EJS
var bgcolor = '#ececec', txtcolor = '#333';
switch( helpers.rowData.category ){
  case 'category-a':
    bgcolor = '#8fc31f';
    break;
  case 'category-b':
    bgcolor = '#fff100';
    break;
  case 'category-c':
    bgcolor = '#e60012';
    txtcolor = '#fff';
    break;
  case 'category-d':
    bgcolor = '#f39800';
    break;
  case 'category-e':
    bgcolor = '#e4007f';
    txtcolor = '#fff';
    break;
}
item.css({ backgroundColor: bgcolor, color: txtcolor });
EJS;
    if ( ! isset( $component_options['customRowScripts'] ) ) {
      $component_options['customRowScripts'] = array( $_custom_script );
    } else {
      $component_options['customRowScripts'][] = $_custom_script;
    }
  }

  return $component_options;
}
add_filter( 'cdbt_shortcode_custom_component_options', 'custom_wp_products_cdbt_shortcode_custom_component_options', 10, 3 );

そして、上記のフィルターフックでショートコードで出力される一覧表の列と行についてカスタマイズを行う。
このカスタマイズが反映されたショートコード[cdbt-view]は下記のように表示される。

データ登録フォームをカスタマイズする

今度は[cdbt-entry]で出力されるデータ登録フォームの表示をカスタマイズする。
登録フォームの表示をカスタマイズする時には、CDBTプラグインに用意されているcdbt_shortcode_custom_formsフィルターを使う。フィルター処理の追加場所はテーマのfunctions.phpだ。

今回のプロジェクトでは、製品評価フォームに対して次のようなカスタマイズを行ってみよう。

評価対象を入力するproduct_idフィールドについて、製品管理テーブルに存在する製品IDのみが入力されるように選択式フィールドに変更する。
入力項目の並び順を変更する。
評価フィールドを10段階評価の選択式フィールドに変更する。
評価コメントフィールドの初期サイズを変更し、補足テキストを追加する。
各フィールドの項目ラベルを変更する。

function custom_wp_product_repute_cdbt_shortcode_custom_forms( $elements_options, $shortcode_name, $table ){
  if ( 'Product evaluation page' === get_post()->post_title && 'cdbt-entry' === $shortcode_name && 'wp_product_repute' === $table ) {
    global $cdbt;
    $result = $cdbt->get_data( 'wp_products', ['ID','product_name'], [], ['product_name'=>'ASC'], 'ARRAY_A' );
    $values = [];
    foreach ( $result as $_i => $_v ) {
      $values[] = $_v['product_name'] .':'. $_v['ID'];
    }
    foreach ( $elements_options as $_i => $_option ) {
      if ( 'product_id' === $_option['elementName'] ) {
        $elements_options[$_i]['elementLabel'] = __('Product Name');
        $elements_options[$_i]['elementType'] = 'select';
        $elements_options[$_i]['selectableList'] = implode( ',', $values );
        $elements_options[$_i]['helperText'] = __('Please choose the name of product to evaluate.');
        $_new_elements_options[0] = $elements_options[$_i];
      }
      if ( 'rater' === $_option['elementName'] ) {
        $elements_options[$_i]['elementLabel'] = __('Your Name');
        $elements_options[$_i]['elementSize'] = 6;
        $_new_elements_options[2] = $elements_options[$_i];
      }
      if ( 'score' === $_option['elementName'] ) {
        $elements_options[$_i]['elementLabel'] = __('Your Score');
        $elements_options[$_i]['elementType'] = 'select';
        $elements_options[$_i]['selectableList'] = '1:1,2:2,3:3,4:4,5:5,6:6,7:7,8:8,9:9,10:10';
        $elements_options[$_i]['defaultValue'] = '5';
        $elements_options[$_i]['helperText'] = __('On a scale of one to ten, how would you rate that product?');
        $_new_elements_options[1] = $elements_options[$_i];
      }
      if ( 'comment' === $_option['elementName'] ) {
        $elements_options[$_i]['helperText'] = __('Please enter here if you have any comments.');
        $elements_options[$_i]['elementExtras'] = array( 'rows' => 4 );
        $_new_elements_options[3] = $elements_options[$_i];
      }
    }
    $_new_orders = array( 1, 3, 2, 4 );
    array_multisort( $_new_orders, $elements_options );
  }
  return $elements_options;
}
add_filter( 'cdbt_shortcode_custom_forms', 'custom_wp_product_repute_cdbt_shortcode_custom_forms', 10, 3 );

このカスタマイズが反映されたショートコード[cdbt-entry]は下記のように表示されるはずだ。

これで製品管理／製品評価プロジェクトは完成だ。
このサンプルプロジェクトは、デモサイト「http://demo.ka2.org」で公開中している。
実際にそちらのデモで製品登録や製品評価が試せるので、やってみてほしい。