Lesson 6

データベースの操作

Lesson 6 Chapter 1
検索

Lesson5で出てきたものもありますが、このレッスンでより深くCakePHPを通してのデータベース操作について学んでいきます。様々なメソッドが出てくるため、実際に動かしながら学ぶためにレッスンの最後で主要なメソッドの型を記述したコードを置いています。各チャプターの説明を読んだ後にコードの内容を変えたりして復習をしてみて下さい。

find()

findメソッドは Lesson5 の indexアクションでも使用していました。

public function index()
{
  $tasks = $this->Tasks->find('all');
  $this->set('tasks',$tasks);
}

上記では、Tasksクラスのメソッドとしてfind()を使用しており、'all'を使うことで、Tasksテーブルに入っているレコードデータを全て取得出来るようにしています。

ただし、「$tasks = $this->Tasks->find('all');」としたときに、$tasksに代入されているのはレコードデータそのものではありません。find()メソッドで返される値は cake/ORM/Queryクラスのオブジェクトとなります。以下に$tasksの値をデバッグ表示したものを載せますので確認してみましょう。

/*

find('all')で返される値を確認するため
$tasksの値をpr()というデバッグ関数で表示させる。

$tasks = $this->Tasks->find('all'); 
pr($tasks); 

以下、$tasksの内容

*/

Cake\ORM\Query Object
(
    [(help)] => This is a Query object, to get the results execute or iterate it.

    [sql] =>
    SELECT
      Tasks.id AS Tasks__id, 
      Tasks.content AS Tasks__content, 
      Tasks.created AS Tasks__created, 
      Tasks.modified AS Tasks__modified 
    
    FROM tasks Tasks

...

    [repository] => App\Model\Table\TasksTable Object
        (
            [registryAlias] => Tasks
            [table] => tasks
            [alias] => Tasks
            [entityClass] => App\Model\Entity\Task
            [associations] => Array
                (
                    [0] => Users
                )

            [behaviors] => Array
                (
                )
...
        )

)

少し見づらいかもしれませんが、$tasksの値を省略して載せていますので、上から見ていましょう。

まず「Cake\ORM\Query Object」の表示があり、find()メソッドで返ってきている値が「Queryクラスのオブジェクト」になっていることがわかります。続いて、オブジェクトが持っている各プロパティが連想配列として格納されています。

  • [(help)]

    最初の「help」には、「これはオブジェクトであり、結果データを取得する場合にはSQL文を実行するための処理をしてください」といったことが書かれています。

  • [sql]

    ここに、tasksテーブルのデータを全て取得する内容のSQL文が格納されています。このSQL文が実行されることによって、実際にレコードデータが取得されることになります。

  • [repository] => App\Model\Table\TasksTable Object

    Tasksテーブルに設定されている情報を表示しています。例えば、「registryAlias」の値がTasksとなっていますが、このエイリアスを使用することで「this->Tasks」のようにコントローラでTasksクラスのオブジェクトを呼び出すことが可能となります。

    他に、TasksTableクラスとマッピングされているDB上のテーブル名や、使用するエンティティクラスが指定されていたりします。

このように、テーブルクラス->find()メソッドが返す値は「Cake\ORM\Queryのオブジェクト」となっており、テーブルクラスの情報とともに、テーブル内を検索するための「SQL文」が格納されています。

つまり「find('all')」は対象のテーブルの全レコードを取得するためのＳＱＬ文を持つオブジェクトを返している、ということが分かるかと思います。

ここで一度、Queryオブジェクトに書かれているSQL文が本当にすべてのレコードを取得するのか、Sサーバー上で実行して確認してみましょう。

コマンドライン上でSQLサーバにログインし、オブジェクトに入っていたSQL文をそのまま実行します。（MySQLへのログインはコマンドライン上で 「MySQL -u root -u」を入力後にパスワード入力です）

SELECT 
  Tasks.id AS Tasks__id, 
  Tasks.content AS Tasks__content,  
  Tasks.created AS Tasks__created, 
  Tasks.modified AS Tasks__modified 
FROM 
  tasks Tasks;

6_1_1.png

全カラムで全てのレコードが表示されたかと思います。（取得されたレコードデータは上記の画像と一致している必要はありません）

Lesson5で作成した、タスク一覧画面でも同様に全てのレコードが表示されているはずなので、ブラウザで開いて見比べてみましょう。(modifiedのカラムはタスク一覧画面で表示していません)

また、ブラウザの下部にCakePHPが表示している赤色のメニューがあるかと思います。この中で「Sql Log」をクリックしてみて下さい。タスク一覧画面を表示するために実行されたSQL文が確認できます。

6_1_2.png

現在のページでどのようなSQLが実行されているのか簡単に確認することが出来るので覚えておきましょう。

また、find()メソッドの引数には'all'のほかによく使われるものとして'list' と 'thread'があるので、この後それぞれ詳しく見ていきます。

list

続いて、find('list')についてです。これは取得したデータを「キー=>値」というインデックス付きの連想配列の形にして、リスト形式で表示することを目的として使用します。わかりにくいと思いますので、先にtasksテーブルのデータを find('list') を使って取得した場合の例を示します。

Array
(
    [1] => データベースの接続を完了する。
    [2] => テスト
    [4] => テストテスト
)

このように配列として、「キー=>値」が並んだリスト形式のデータを取得することが出来ます。

上記の例ではキーに「id」フィールドを指定し、対応する値に「content」フィールドを指定してfind('list')を使用しています。このキーと値の設定をするには２通りの方法があります。

１つ目は find('list') のオプションとして第二引数にvalueFieldを指定する方法です。

find('list',['valueField'=>'content']);

上記では「キー=>値」の「値」にあたる「valueField」のみを指定しています。キーを選択する場合には「'keyField'=>'id'」のように「keyField」を使用する必要がありますが、デフォルトでテーブルの主キーとなるフィールドが選択されるため、変更する必要が無い場合には「valueField」のみの指定で問題ありません。

2つ目の方法は、modelのテーブルクラス内でdisplayFieldを指定する方法です。

今回だとsrc/Model/Table/TasksTable.phpにinitializeメソッドを追加して、displayFieldの指定を入れることになります。

TasksTable.php
class TasksTable extends Table
{
  public function initialize(array $config): void
  {
      $this->setDisplayField('content');
  }

この場合には、キーにはデフォルトである主キーのフィールドが設定され、「find('list')」の記述のみで「content」を値としたリストを作ることが出来ます。

ここまで、find('list')の使い方を見てきましたが、fine('all')と同様に、find('list')が返す値も Queryオブジェクトであり、オブジェクトには以下のようなSQL文がセットされています。

SELECT 
  Tasks.id AS Tasks__id, 
  Tasks.content AS Tasks__content 
FROM 
  tasks Tasks

内容はtasksテーブルの全てのレコードを対象に、「id」カラムと「content」カラムの値のみを取り出すものです。

このSQL文を実行して、リスト形式の配列データを取得するには、更にQueryオブジェクトが持っているメソッドを使用する必要があり、以下の「toArray()」と「toList()」の２つが使用されます。

$query = $this->Tasks->find('list'); //$queryにQueryオブジェクトが返される
$list = $query->toArray() //toArray()を使用

//$listには以下の値が代入される
Array
(
    [1] => データベースの接続を完了する。
    [2] => テスト
    [4] => テストテスト
)
$query = $this->Tasks->find('list'); 
$query->toList() //toList()を使用
  
//$listには以下の値が代入される
Array
(
    [0] => データベースの接続を完了する。
    [1] => テスト
    [2] => テストテスト
)

取得できた配列データは一見変わらないように見えますが、キーの値を比べてみると、toArray()の場合には「主キーであるidフィールドの値」が使用されており、toList()の場合には 「0 から順にインデックスとして番号が振りなおされる」という違いがあります。

find('list')はセレクトボックスを作成するときなどに便利ですが、その時々に合わせて必要な値が取得できるよう「toArray()」と「toList()」を使い分けて使用します。

threaded

find('threaded')は、「隣接リスト」と呼ばれる構造で保存されているテーブルのデータを取得する際に使用されます。

例えば、コメント機能を持つアプリケーションを開発しており、１つ１つのコメントをcommentsテーブルにレコードとして保存し管理しているとします。

comentsテーブル
id coment parent_id
1 お元気ですか？ null
2 はい、元気です 1
3 よかったです。 2

このとき、上記テーブルのように parent_id というカラムを用意しておき、返信コメントが追加される場合には parent_id に親となるコメントの id を保存するようにします。上記の例だと、id = 2 のコメントの parent_id が 1 となっていることから、id = 2 のコメントはid = 1 への返信コメントであることがわかります。

このように「同テーブル内で親と子の関係を持つ構造」である「隣接リスト」を持つテーブルに対して、find('threaded')メソッドを使うことで、親と子の関係を多次元配列で表すことが出来るようになります。

find('threaded')の使い方ですが、引数にはオプションの設定が必要となります。

$coments = $this->Coments->find('threaded', [
  'fields' => ['id','coment','parent_id' ]
]);

上記のように、オプションとしてfields を使用し、子レコードのどのフィールドを表示するか設定します。この時、親子関係を表すために必要な、id と parent_id は必ず含める必要があります。

comentsテーブルがあると仮定して、find('threaded')を使用した場合、以下のような値を取得することができます。

$coments = $this->Coments->find('threaded', [
  'fields' => ['id','coment','parent_id' ]
]);

$data = $coments->toArray();

//$dataには以下のように値が代入される
Array
(
    [0] => App\Model\Entity\Coment Object
        (
            [id] => 1
            [content] => お元気ですか
            [parent_id] => 
            [children] => Array
                (
                    [0] => App\Model\Entity\Coment Object
                        (
                            [id] => 2
                            [content] => はい元気です
                            [parent_id] => 1
                            [children] => Array
                                (
                                    [0] => App\Model\Entity\Coment Object
                                        (
                                            [id] => 4
                                            [content] => よかったです
                                            [parent_id] => 2
                                            [children] => Array
                                                (
                                                )
                                                ...

このように多次元配列として取得することが可能となり、「子」にあたるレコードのデータは自動で生成された「children」というキーの値として格納されていることがわかります。

スレッド形式のデータは上記のようなコメントのデータ取得や、階層を持つメニューバーなどの機能を作成する際などに活用することができます。

動的ファインダー

find()メソッドの別の形として「動的ファインダー」と呼ばれるものがあり、Lesson5 で edit アクションや delete アクションを作成したときに、findById($id)という形で使用しました。

TasksController.php
public function edit($id)
{
  $task = $this->Tasks->findById($id)->firstOrFail();
  if ($this->request->is('post')) {
    ...

「Tasks->findById($id)」となっているので、一見するとtasksテーブルクラスのオブジェクトから呼び出しているように見えますが、これはfindById()というメソッドがあるわけではなく、find の後の部分をCakePHPが動的に判断して処理を行っています。

そのため、findByの後の「Id」部分を別のフィールド名に変更して使ったり、「and」や「or」というものを使って、以下のように複数のフィールドを指定することも可能です。 

// id = 1 かつ content = 'テスト' であるレコードを取得する
$tasks = $this->Tasks->findByIdAndContent('1','テスト ');

// id = 1 もしくは content = 'テスト' であるレコードを取得する
$tasks = $this->Tasks->findByIdOrContent('1','テスト ');

この２つの返す結果を比べると、「And」ではid と content 両方の値が一致するものを取得しますが、「Or」の場合はid もしく content のどちらかだけでも一致するものがあれば取得します。

例として、以下のtasksテーブルに対してandとor、それぞれの動的ファインダーを実行した場合に実行されるSQL文を示します。 

mysql> select id, content from tasks;
+----+--------------------------------+
| id | content                        |
+----+--------------------------------+
|  1 | データベースの接続を完了する。 |
|  2 | テスト                         |
|  4 | テストテスト                   |
+----+--------------------------------+
$tasks = $this->Tasks->findByIdAndContent('1','テスト');
$data = $tasks->toArray();
pr($data);
//実行されるSQL文
SELECT 
  Tasks.id AS Tasks__id, 
  Tasks.content AS Tasks__content,  
  Tasks.created AS Tasks__created, 
  Tasks.modified AS Tasks__modified 
FROM 
  tasks Tasks 
WHERE 
  (
    Tasks.id = 1 
    AND Tasks.content = 'テスト'
  )

SQLを見てみると、基本はfind('all')で実行されていたものと変わりありませんが、SELECT と FROM の後に WHERE が記述されていることがわかります。

このWHERE句は、どのような条件でデータを検索するかを記述する部分であり、今回は「id = 1」と「content = 'テスト'」の間に「AND」が使用されています。

今回のtasksテーブルのデータを見ると「id = 1」 かつ「content = 'テスト'」となっているレコードは無いため、「AND」の条件は満たせず、取得するレコードは無しとなります。

//取得されたデータ

Array
(
  //何も取得されない
)

次に「Or」を使用した場合ですが、WHERE句において「OR」が使われ、id = 1 のレコードも、content = "テスト"のレコードも存在するため、２つのレコードが取得されることになります。

$tasks = $this->Tasks->findByIdOrContent('1','テスト');
$data = $tasks->toArray();
pr($data);
//実行されるSQL文

  SELECT 
  Tasks.id AS Tasks__id, 
  Tasks.content AS Tasks__content, 
  Tasks.created AS Tasks__created, 
  Tasks.modified AS Tasks__modified 
FROM 
  tasks Tasks 
WHERE 
  (
    Tasks.id = 1 
    OR Tasks.content = 'テスト'
  )
//取得されたデータ

Array
(
    [0] => App\Model\Entity\Task Object
        (
            [id] => 1
            [content] => データベースの接続を完了する。
                  ...
        )
    [1] => App\Model\Entity\Task Object
        (
            [id] => 2
            [content] => テスト
                  ...
        )
)

このように「動的ファインダー」はSQL文におけるWHERE句の内容を、フィールド名を使ってシンプルに指定することができるため、コードの可読性が高めながら、期待する値も簡単に取得することが出来る便利な検索方法となっています。

Lesson 6 Chapter 2
追加、更新、削除

save()

saveメソッドはsave($entity)の形で、新規のエンティティ、もしくは更新されたエンティティのデータを引数にとり、テーブルクラスにデータを渡すことで保存を行っています。Lesson5でも、addとeditアクションの作成で使ってきました。

TasksController.php
public function add()
{
  $task = $this->Tasks->newEmptyEntity();
    if ($this->request->is('post')) {
        $this->Tasks->patchEntity($task, $this->request->getData());
        // addアクションでは「新規」のエンティティを引数に取っています
        if ($this->Tasks->save($task)) {
            $this->Flash->success('タスクが登録されました');
            return $this->redirect(['action' => 'index']);
        }
        $this->Flash->error('新規作成に失敗しました');
    }
    $this->set('task', $task);
}

保存するエンティティが「新規」か「更新」かの判断ですが、CakePHPではnewEntity() もしくは newEnptyEntity()メソッドを使ってエンティティの作成に成功している場合には「新規」。find()メソッドで既存のエンティティを取得している場合には「更新」として判断をします。

save()メソッドが実行された時にどのようなSQL文が実行されているかですが、「新規」と「更新」でそれぞれ異なるSQL文が使用されています。tasksテーブルで使用した場合の例を以下に示します。 

//新規の場合
て
INSERT INTO tasks (content) VALUES ('データベースの接続を完了する。');
//更新の場合

UPDATE tasks SET content = 'テスト' WHERE id = 2;

新規では、インサートが使用され、テーブル名と投入するデータのカラム名とその値を指定しています。

更新では、アップデートが使用され、テーブル名、カラム名、値の他にWHERE句が入っており、既存のどのレコードの値を更新するかを指定しています。

delete()

delete($entity)のように、deleteメソッドもsaveメソッド同様の形で、削除するエンティティを引数に指定して使用します。Lesson5 の deleteアクションでは以下のように使用していました。

TasksController.php
public function delete($id)
  {
    $task = $this->Tasks->findById($id)->firstOrFail();
    if ($this->Tasks->delete($task)) {
        $this->Flash->success('id '.$task->id.' を削除しました');
        return $this->redirect(['action' => 'index']);
    }
  }

delete()メソッドを使用したとき、内部的に発行されるSQL文は以下のようなものになります。

DELETE FROM tasks WHERE id = 2

この例ではtasksテーブルの id = 2 のレコードを削除しています。

save()メソッド、delete()メソッドともに、実行される前にはデータチェックなど、正常に処理を行うことができるかを確認する処理が入り、問題がなければ実行・保存が行われ、仮に問題が起こった場合には処理を停止し、処理前の状態にもどすように働きます。

Lesson 6 Chapter 3
クエリービルダー

Queryクラス

findメソッドを使用した時点で返ってくる値は「Queryクラスのオブジェクト」であるということについては Chapter1 でも触れましたが、このQueryクラスにはテーブル内のデータ検索をより細かい条件で行うための様々なメソッドも含まれています。

そのため、findメソッドで返ってきたQueryクラスのオブジェクトを操作することで、さまざまな検索条件のSQL文を作成することが可能となり、より無駄なく必要なデータを取得することが出来るようになります。

Queryクラスのメソッド

さっそく、検索条件を指定するQueryクラスのメソッドを主要なものにしぼって見ていきましょう。

  • select

    取得するフィールドを制限することが出来ます。例えばidとcontentのみを取得したい場合には以下のように記述します。

    //findによってQueryクラスのインスタンスが返される
$query = $tasks->find();
//Queryクラスのselectメソッドでidとcontentを指定
$data = $query->select(['id', 'content']);
  • where

    連想配列の形で指定したフィールドとその値に合致するデータを検索することができます。「id が 1」という条件でデータを検索したい場合は以下のようになります。

    $query = $tasks->find();
$data = $query->where(['id' => '1']);

    複数の条件を指定したい場合にはwhereを複数回呼ぶことも可能です。

    $data = $query->where(['フィールド1'=>'値'])->where(['フィールド2'=>'値'])
  • order

    検索したデータの並び変えを行うときに使用します。並び変えの種類としては「昇順(ASC)・降順(DESC)」の2種類になります。

    idを降順で取得したい場合には以下のようになります。

    $query = $tasks->find();
$data = $query->order(['id' => 'DESC']);

ここからはQueryクラスからfunc()メソッドを呼ぶことによって使用可能となるSQL関数について見ていきます。以下の関数は検索データ全体を元に一つの値を求める内容となっており、基本的にはselectメソッドの中で展開し1行のデータとして取得します。わかりにくいと思いますので例を見ながら理解していきましょう。

  • min

    対象のフィールドの中で最も小さい値を持つデータを取得します。idを対象とする場合は以下の形で呼び出せます。

    $query = $tasks->find();
$data = $query->select(['min'=>$query->func()->min('id')])->first();

    selectメソッドの引数に連想配列が来ていることが分かるかと思います。ここでの'min'はテーブルに存在するフィールド名ではなく、表示用にこの場限りで使用するものとなります。minというフィールドを一時的に作成し、そこにidフィールドの中でもっとも小さな値を入れるという形です。取得されるデータは'min'=>'idの最小値'という連想配列１つのみとなります。

    mysql> select id, content from tasks;
+----+--------------------------------+
| id | content                        |
+----+--------------------------------+
|  1 | データベースの接続を完了する。 |
|  2 | テスト                         |
|  4 | テストテスト                   |
+----+-------------------------------+

    上記テーブルに対して使用した場合、以下のように結果が返ります。

    App\Model\Entity\Task Object
(
    [min] => 1
    ...

    このように、min の値として最小値のデータのみを返します。

    以下の関数も呼び出す形は同様となります。「min」の部分を別の関数名に替えることで使用することができます。

  • max

    対象のフィールドの中で最も大きい値を持つデータを取得します。

  • sum

    対象のフィールドの検索結果全ての値を合計して返します。

  • avg

    対象のフィールドの検索結果全ての値の平均を返します。

  • count

    対象のフィールドがいくつのデータを持っているのかを数えます。以下のようにcountの対象を'*'とすることでテーブルの全データを対象として検索します。

    $query = $tasks->find();
$tasks = $query ->select(['count'=>$query->func()->count('*')],'id','content')->first();

メソッドチェーンで結果を操作する

ここまで見てきたQueryクラスのメソッドを複数使って検索を行いたい場合に、メソッドチェーンというものを使用する方法があります。まずは以下の形を見てください。

$query = $this->Tasks->find();//Queryクラスのインスタンスを作成
$tasks = $query ->select(['id','content']) //id,contentフィールドのみ取得
                ->where(['id >='=>'2'])  //idが2以上のものだけを対象に
                ->order(['id'=>'DESC'])  //idを降順で取得
                ->toList();              //クエリーが実行されリスト形式でデータを取得

一つのメソッドを使用した後すぐにアロー演算子(->)が続いており、メソッドが連続して呼び出されているのがわかるかと思います。このようにチェーンのようにつなげていく記述のためにチェーンメソッドと呼ばれています。

上記のチェーンメソッドを使用した時に実行されるSQL文は以下のようになります。

SELECT 
  Tasks.id AS Tasks__id, 
  Tasks.content AS Tasks__content 
FROM 
  tasks Tasks 
WHERE 
  id >= 2 
ORDER BY 
  id DESC

チェーンメソッドを使用することで、SQL文を記述する場合と同じように検索条件を指定することができるため、取得するデータの条件をより細かく設定したい場合などに重宝する検索方法となります。

クエリーの遅延評価について

クエリーの遅延評価とは、ある一定の状態になるまでは、「クエリー」つまりはQueryオブジェクトにセットされているSQL文が実行されないことを意味します。

例えば、１つ前で見てきた「メソッドチェーン」では何度もメソッドが呼ばれていましたが、そのたびに SQL文が実行されているわけではありません。各メソッドが呼ばれる度にSQL文の内容が変更されていき、その後の結果を取得するための toArray()のようなメソッドが呼ばれた時に初めてSQL文が実行されます。

このように、検索条件を追加するメソッドが呼ばれている間はSQL文が実行されず、その後の結果取得時に実行されることを「クエリーの遅延評価」と言います。

SQL文が実行されるタイミングですが、具体的には以下の時点となります。

  • all()メソッドが呼ばれる
  • first()もしくはfirstOrFail()が呼ばれる
  • toList() か toArray() メソッドが呼ばれる
  • foreach() で取り出される(=イテレート)される

これまでにも何度も触れてきましたが、find()などで取得した「Queryオブジェクト」を使って「検索条件」を整え、上記のようなSQL文の実行を行うメソッドをつかって必要な値を取得することが出来る、ということを覚えておきましょう。

「検索」の練習

ここまで様々なメソッドを見てきましたが、読むだけでは理解しづらい部分もあるかと思います。そのため、クエリー関連のメソッドを以下にまとめました。想定としてはtasksテーブルを対象にして記述してあるため、メソッドの動きを確認するだけであればTasksContoroller.phpのindexアクションにコピー＆ペーストをして使用することができます。

元に戻せるか不安な場合には、ファイルごとコピーを取っておきましょう。または、次のレッスンでMVCのひな形を簡単に作ることの出来る「bakeコマンド」を学ぶので、その後で新しく練習用の環境を用意してみるのも良いと思います。

TasksController.php
public function index()
  {
    //検索の種類
    $tasks = $this->Tasks->find('all');
    //$tasks = $this->Tasks->find('list',['valueField'=>'content']);

    //動的ファインダー
    //$tasks = $this->Tasks->findByIdAndContent('1','テスト');
    //$tasks = $this->Tasks->findByIdOrContent('1','テスト');

    //チェーンメソッドの使用例
    /*
    $query = $this->Tasks->find();
    $tasks = $query ->select(['id','content'])
                    ->where(['id >='=>'5'])
                    ->order(['id'=>'DESC']);
    */

    //func()の使用例
    /*
    $query = $this->Tasks->find();
    $tasks = $query->select(['max'=>$query->func()->max('id')]);
    */

    //クエリーが実行されるメソッド
    //$tasks = $tasks->all();
    $tasks = $tasks->toArray();//配列への変換
    //$tasks = $tasks->first();//フェッチしたとき
    //$tasks = $tasks->toList();
    
    pr($tasks);//デバッグ機能を追加

    $this->set('tasks',$tasks);
  }

コメントアウトを利用して様々な組み合わせで試してみてください。TasksControllerのindexアクションで試す場合は、ブラウザでタスク一覧の画面を読み込むとpr()メソッドが捉えた$tasksのデータを画面の一番上にデバッグ内容として表示します。