通常のテーブルでの検索、FTSテーブルの検索と紹介してきましたが、実際にはこれらのテーブルを結合したり、サブクエリーを使用して検索を行います
この辺りを使いこなせるようになるといろいろ複雑な処理も出来るようになってくると思いますので、もうひと頑張りです (^^)
テーブル結合
SQLite: Joins
www.techonthenet.com先ほど 4/1以降に登録されたプラグインをダウンロード数が多い順に取得するSQLをやってみましたが、この時に作者情報も同時に取得したい場合は INNER JOIN を使い作者IDを関連付けてデータを取得します
SELECT * FROM plugins_base As p INNER JOIN plugins_author AS a ON p.author_id = a.id WHERE p.added > '2016-04-01 00:00:00' ORDER BY p.downloaded DESC LIMIT 0, 10
実行すれば、プラグインデータと合わせて作者データも取得出来るのが確認出来たと思います
さらにFTSテーブルを結合することも出来ます
例えば、プラグインの名前かスラッグに ‘google photo’ というワードで全文検索してみます
この場合はプラグインの情報を取得するのに３つのテーブルを結合しています
SELECT p.*,a.* FROM (plugins_base AS p INNER JOIN plugins_fts AS f ON p.id = f.docid ) INNER JOIN plugins_author AS a ON p.author_id = a.id WHERE f.names MATCH 'google photo' ORDER BY p.downloaded DESC
※JOIN 以外でもクエリを結合すれば複数のテーブルや複数のカラムからデータを抽出するのに INTERSECT, EXCEPT, UNION 等のSQL組み合わせて使用できます
集合のベン図をイメージすると分かりやすいです
![[SQL] 8. クエリの結合](https://celtislab.net/wp-content/uploads/celtispack/thumbnail/fa82d969ec0e4d1b7183300c1aeed31e.gif){kind=small}
TECHSCOREサブクエリー
DELETE のところでも少し紹介しましたが、JOINでうまく処理出来ない場合にサブクエリーを使って記述するケースもあります
また、先ほど３つのテーブルを結合して検索させてみましたが、サブクエリーを使って書き換えることも出来ます
SELECT * FROM plugins_base As p INNER JOIN plugins_author AS a ON p.author_id = a.id WHERE p.id IN (SELECT docid FROM plugins_fts AS f WHERE f.names MATCH 'google photo') ORDER BY p.downloaded DESC
結果は同じで、パフォーマンスもほとんど変わらないようです
私には、サブクエリーを使った記述のほうが読みやすく感じます (^^)
チューニング
SELECT文が複雑になってくると思ったようにパフォーマンスが出ない場合があります
EXPLAIN QUERY PLAN コマンドで SELECT 文を実行すると内部処理を確認することが出来ます
正直なところ見方はよくわかっていないのですがインデックスの使用状況が確認できます ^_^;
パフォーマンスが出ない場合は、期待しているインデックスが使用されていない場合もありますので INDEXED BY を使ってインデックスを指定することも可能です
集計関数
集計関数 SQLiteでも様々な集計関数が使用できます
カウントの取得を紹介します
SELECT COUNT(id) FROM plugins_base WHERE plugins_base.last_updated > '2016-04-01 00:00:00'
※カウント取得は、指定カラムの特定の条件を満たすデータ数を取得する場合等に使用しますが、条件によっては結構時間がかかりますので使い所には注意したほうが良いです
別の例として、重複名称データを抽出してみます
サンプルのデータベースのプラグインの slug カラムには UNIQUE 制約が付いていますが、name カラムには UNIQUE 制約がついていません
その為に、slug は別なのに同じ名前のプラグインが存在してしまっています
GROUP BY を使い重複する名前を取得してみます
SELECT name, COUNT(*) FROM plugins_base GROUP BY name HAVING COUNT(*) > 1
Ext_PDO を使った場合
様々な SELECT 文を紹介してきましたが、プログラムとして記述した場合の例も紹介しておきます
- sql_prepare_results メソッド ： 複数データ取得
- sql_prepare_row メソッド ： 単一レコードデータ取得
- sql_get_var メソッド ： カウントのような単一カラムデータの取得
例えば、検索ワード gallery という入力があった場合は、入力データ gallery がPHPの $skey 変数にセットされる用に処理して、それを sql_prepare_results メソッドに渡して実行し、結果を取得します
$plugins = $db->sql_prepare_results("SELECT * FROM plugins_base As p INNER JOIN plugins_author AS a ON p.author_id = a.id WHERE p.id IN (SELECT docid FROM plugins_fts WHERE plugins_fts.names MATCH ? ) ORDER BY p.downloaded DESC LIMIT 0, 100", array($skey));
単純化した形で紹介していますが、実際は様々な検索条件毎に生成するSQL文が変わっていますので、こんな感じのプログラムコードとなっています
/**
* 指定クエリー条件からプラグイン情報オブジェクトを生成する
*
* @param type $query array('type'=>$type, 'order'=>$order, 'skey'=>$skey, 'paged'=>$page)
* @return type
*/
public function get_pluginslist($query) {
$obj = new stdClass();
$type = $query['type'];
$order = $query['order'];
$skey = $query['skey'];
$paged = (empty($query['paged']))? 1 : $query['paged'];
//SQL 構築
$sel = array('names', 'tags', 'authors');
$now = new DateTime();
$option = '';
$index = '';
$limit = '';
//種別
if(in_array($type, $sel)){
//$skey 入力データはSQLエスケープが必要なので？をセット
if($type == 'tags')
$sitem = "plugins_fts MATCH ?";
else {
$sitem = "plugins_fts.$type MATCH ?";
}
$index = "INDEXED BY $order ";
if($order == 'download'){
$limit = "ORDER BY p.downloaded DESC LIMIT 0, 100 ";
}
elseif($order == 'installs'){
$limit = "ORDER BY p.active_installs DESC LIMIT 0, 100 ";
}
elseif($order == 'review'){
$limit = "ORDER BY p.num_ratings DESC LIMIT 0, 100 ";
}
$option = "WHERE p.id IN (SELECT docid FROM plugins_fts WHERE $sitem) ";
}
elseif($type == 'newpickup'){
//SELECT * FROM plugins_base As p INNER JOIN plugins_author AS a ON p.author_id = a.id WHERE p.added > '2016-01-25 00:00:00' ORDER BY p.downloaded DESC LIMIT 0, 100
$sdate = $now->modify("-3 month")->format('Y-m-d');
$option = "WHERE p.added > '$sdate 00:00:00' ";
$limit = "ORDER BY p.downloaded DESC LIMIT 0, 100 ";
}
elseif($type == 'japan'){ //最大5ページ(500)
$option = "WHERE p.country = 'Japan' ";
$limit = "ORDER BY p.downloaded DESC LIMIT 0, 500 ";
}
else{ //popular 最大5ページ(500)
$option = "";
$limit = "ORDER BY p.downloaded DESC LIMIT 0, 500 ";
}
//データ数取得
timer_start(); //検索時間測定
$obj->query = $query;
if(!in_array($type, $sel)){
//SELECT * FROM plugins_base As p INNER JOIN plugins_author AS a ON p.author_id = a.id ORDER BY p.downloaded DESC LIMIT 0, 20
$obj->plugins = $this->db->sql_get_results("SELECT * FROM $this->plugins_table As p INNER JOIN $this->authors_table AS a ON p.author_id = a.id $option $limit");
}
elseif(!empty($skey)){
//SELECT * FROM plugins_base As p INDEXED BY download INNER JOIN plugins_author AS a ON p.author_id = a.id WHERE p.id IN (SELECT docid FROM plugins_fts AS f WHERE f.tags MATCH 'youtube') ORDER BY p.downloaded DESC LIMIT 0, 100
$obj->plugins = $this->db->sql_prepare_results("SELECT * FROM $this->plugins_table As p $index INNER JOIN $this->authors_table AS a ON p.author_id = a.id $option $limit", array($skey));
}
else {
$obj->plugins = false;
}
$obj->query_time = timer_stop(); //検索時間測定
if(is_array($obj->plugins)){
$count = count($obj->plugins);
$obj->pages = ($count % 100 == 0)? $count / 100 : (int)($count / 100) + 1;
$obj->paged = $paged;
if($obj->pages > 1 ){
//該当ページのデータ抽出
$pickup = '';
$offset = ($paged - 1) * 100;
$n = 0;
foreach($obj->plugins as $v){
if($n >= $offset && $n < $offset + 100){
$pickup[$n] = $v;
}
$n++;
}
$obj->plugins = $pickup;
}
}
return $obj;
}
ここで紹介していないSQLコマンドも沢山あるので、後は各自調べてみてください m(_ _)m
データベースクローズ
最後にデータベースのクローズ（接続断）についてです
PDO もしくは Ext_PDO を使用していれば、作成したインスタンスに NULL をセットするとクローズされます。また、明示的にクローズしなくともインスタンスが破棄されるときに自動的にクローズされますので通常はクローズ処理を記述する必要はありません
※異なる環境で SQLite を使用する場合には、明示的なクローズ処理が必要な場合もあります
以上で PDOを使ったSQLite３の全文検索(FTS)入門はおわりです
お疲れ様でした (^^)
これで、小規模な Web アプリケーション開発でSQLite を使う時の基本はマスター出来たと思います。後は、アイデアと日本語の扱いをどうするかだけです (^^)
